SQL : Replace a string starting with specific character

Question

Hi team - If a column value starts with only S or T, I need to remove it. How do I write the SQL query?

For ex, the column values are S19-37373, S98765, T12345, T09-2345

The outcome should be 19-37373, 98765,12345, 09-2345

Thanks

Answer 1

Hi @kkran
There are different ways to remove the first character，some of which have been given by experts.
You can find the used string functions at this link for more details.
As a complement, you can also use the Right function,like this:

 Update #Sample  
 Set MyColumn = RIGHT(MyColumn, LEN(MyColumn) - 1)  
 Where Left(MyColumn, 1) In ('S', 'T');  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Tom, instead of LEFT(myColumn,1) condition you may want to use
myColumn LIKE '[S-T]%'

Also, I was thinking that TRIM function in SQL 2017+ can also work since it allows to specify any combination of characters:

DROP TABLE IF EXISTS #sample;
Create Table #Sample(MyColumn varchar(20));
 Insert #Sample(MyColumn) Values
 ('S19-37373'), 
 ('S98765'), 
 ('T12345'), 
 ('T09-2345');

 SELECT *, TRIM('ST' FROM myColumn) AS newColumn FROM #Sample  s WHERE s.MyColumn LIKE '[S-T]%'

Answer 3

Create Table #Sample(MyColumn varchar(20));
Insert #Sample(MyColumn) Values
('S19-37373'), 
('S98765'), 
('T12345'), 
('T09-2345');

Update #Sample
Set MyColumn = Stuff(MyColumn, 1, 1, '')
Where Left(MyColumn, 1) In ('S', 'T');

-- Check Result
Select * From #Sample;

Tom

Answer 4

Another solution is to use the function SUBSTRING():

UPDATE MyTable
SET Col =  SUBSTRING(Col, 2, LEN(Col) - 1)
WHERE LEFT(Col, 1) IN ('S', 'T');