tsql - extract string using SUBSTRING

Question

I have a column where I need to extract the remainder of a string starting from "_". Example below:

TF_Bskt Trade would need to be "Bskt Trade"
SSC_Equity would need to be "Equity"
etc ......

So I've got this far using the TF_Bskt Trade example:

SELECT SUBSTRING ('TF_Bskt Trade' ,CHARINDEX('_','TF_Bskt Trade')+1, length)

However i'm getting myself confused with how to make sure the length is from the start of after the _ and the end of the string so that it is dynamic? Basically so it caters for varying degrees of string length from after "_".

Any ideas of how to efficiently achieve this?

score 4 · Accepted Answer · edited Jan 02 '14 at 16:15

4

The following will do the job

 SELECT SUBSTRING ('TF_Bskt Trade', CHARINDEX('_', 'TF_Bskt Trade') + 1, LEN('TF_Bskt Trade'))

Because as the documentation for the length parameter of SUBSTRING states "If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.".

edited Jan 02 '14 at 16:15

marc_s

732,580
175
1,330
1,459

answered Jan 02 '14 at 16:01

Paul Hunt

3,395
2
24
39

1

`LEN` doesn't count trailing spaces: `LEN('A ')` returns 1; – Bogdan Sahlean Jan 02 '14 at 16:22
that's fine, i'm not interested in counting trailing spaces – chrissy p Jan 02 '14 at 16:30
why wouldn't it count trailing space oh, because microsoft – user1566694 May 28 '15 at 16:53

tsql - extract string using SUBSTRING

1 Answers1