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?

marc_s · Accepted Answer · 2014-01-02 16:15:57Z

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 2, 2014 at 16:15

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 2, 2014 at 16:01

Paul Hunt

3,6152 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bogdan Sahlean Over a year ago

LEN doesn't count trailing spaces: LEN('A ') returns 1;

chrissy p Over a year ago

that's fine, i'm not interested in counting trailing spaces

user1566694 Over a year ago

why wouldn't it count trailing space oh, because microsoft

Collectives™ on Stack Overflow

tsql - extract string using SUBSTRING

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related