Search code examples
sql-serverselectdynamiccharindex

SQL Server 2008: Finding a value from a string

This might be a very simple select statement, here's my question.

I have a variable 
DECLARE @A varchar(128) --declaring variable
SET @A = 'sus_123456_R5_20140506'  --setting value

I want to find the value after 'sus_' and before 'R5' Also, the value in between is not of fixed length. So the function has to be dynamic. However it always have sus and _R5_date. That's constant.

SET @A = 'sus_129_R5_20150408

Thanks
Solution

  • Use SUBSTRING in combination with CHARINDEX:

    SELECT SUBSTRING(@A,CHARINDEX('sus_',@A,0)+4,CHARINDEX('_R5',@A,0)-5)