SQL To remove first few character from query

Suppose you have to write a query to remove first few character in a column on a SQL Server table.

Col A             Col B
1                    CM NewData
2                   CM Old Data
3                   CM My Data

And we want to remove "CM " cm with space from our result set or use this query to update the column itself.


There are four ways to do this.

Method 1
Substring function: We need three information column name , starting position and ending position. In our example we know column name, starting positing (4th character here) and I can put any ending length would be column length

SELECT SUBSTRING(ColB, 4,1000)
  FROM [dbo].[MyTable]
  WHERE ColB LIKE 'cm %'

Method 2

Use of RIGHT FUNTION: Here we  need column name, total length of column minus what length we need to remove on the right side.


SELECT RIGHT(colB, LEN(colB) - 4)
  FROM [dbo].[MyTable]
  WHERE ColB LIKE 'cm %'

Method 3

Replace Method. In Replace function, we take column name, what we are looking for and what we need to replace with it. Make sure that the pattern you want to replace does not repeat in same string. Otherwise it would replace them too.

SELECT REPLACE(ColB, 'CM ','')
  FROM [dbo].[MyTable]
  WHERE ColB LIKE 'cm %'

Method 4

STUFF Method. Stuff method is another way to replace character in a column.

  SELECT Stuff(ColB, 1, 4, '')
    FROM [dbo].[MyTable]

  WHERE ColB LIKE 'cm %'

All of the above method has same execution result and cost factor. Depending on your need, you can use which ever fits your bill.

:)