MySQL how to delete first 3 letters for all records found?

70 views Asked by At

I have column named Column1 looks like this:

ABCABC321
ABCABC213
ABC478
ABC474

Here is a query to show all extra characters of 'ABC'

select * from TABLE where Column like 'ABCABC%';

This is the output:

ABCABC321
ABCABC213

How can I delete the extra 'ABC' for all records from the query used above?

This is the desired output:

ABC321
ABC213
ABC478
ABC474
4

There are 4 answers

0
fancyPants On BEST ANSWER
UPDATE your_table
SET your_column = RIGHT(your_column, CHAR_LENGTH(your_column) - 3)
WHERE your_column LIKE 'ABCABC%';
0
Vinoth Raj On

Using STUFF Function:

Update #table_name
set #column_name = Stuff(#column_name, 1, 3, '')
where #column_name like 'ABCABC%';
0
Paul Spiegel On

Use a case condition and the SUBSTRING function to read a string value from the 4th character (ignoring the first 3):

select 
  case when Column1 like 'ABCABC%'
    then substring(Column1, 4)
    else Column1
  end as Column1
from Table1

See demon on db-fiddle

1
Sachin Tripathi On
create function [dbo].[RemoveAllRepeatingChars](
@input_string varchar(2000))
returns varchar(2000)
as 
begin 
declare @output_string varchar(2000)=''
declare @index int
declare @current_char char(1) = 0
declare @previous_chars varchar(2000) = '' 
set @output_string = ''
set @index = 1 
--traverse input string
while @index <= len(@input_string) 
begin          
    --get current character in string
    set @current_char = substring(@input_string, @index, 1) 
    if charindex(@current_char, @previous_chars) = 0
    begin   
        set @output_string = @output_string + substring(@input_string, @index, 1)                          
    end    
    -- store all the characters for comparison
    set @previous_chars = @previous_chars + substring(@input_string, @index, 1)    
    set @index = @index + 1    
end
return @output_string
end
*****************************************************************
UPDATE your_table SET your_column = (select RemoveAllRepeatingChars(your_column)) WHERE your_column LIKE 'ABCABC%';