tsql - extract string using SUBSTRING

sql-server-2008 tsql substring charindex
chrissy p picture chrissy p · Jan 2, 2014 · Viewed 8k times · Source

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?

Answer

Paul Hunt picture Paul Hunt · Jan 2, 2014

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.".

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More