Select Query

Transact-SQL
1

Hi,

Please help me to get a query, for extracting a particular part from a string

DECLARE @S_Tbl TABLE
(
Source_String varchar(100)
)

INSERT INTO @S_Tbl SELECT '182-84,296-86,325-10,339-87,418-70,455-67,460-18,485-66,489-85'
INSERT INTO @S_Tbl SELECT '169-68,182-84,244-86,325-10,339-87,345-70,454-67,458-18,477-69,484-66,497-85'

In the string first part is the value (string before hyphen ) and the second part is the code (string after hyphen)

From the above table I want get the value of 86

so the result should be
296
244

2

Something like:

select reverse(substring(reverse(substring(source_string,1,charindex('-86,',source_string+',')-1)),1,charindex(',',reverse(substring(source_string,1,charindex('-86,',source_string+',')-1))+',')-1)) as result
  from @s_tbl
 where source_string+',' like '%-86,%'
;
1 Like
3

Thank you very much !

4

Here is an alternate version:

Select s2.Item 
  From @S_Tbl s
 Cross Apply dbo.DelimitedSplit8K(s.Source_String, ',') s1
 Cross Apply dbo.DelimitedSplit8K(s1.Item, '-') s2
 Where s1.Item Like '%-86'
   And s2.ItemNumber = 1

This version removes the wildcard search...

Select s3.Item
  From @S_Tbl s
 Cross Apply dbo.DelimitedSplit8K(s.Source_String, ',') s1
 Cross Apply dbo.DelimitedSplit8K(s1.Item, '-') s2
 Cross Apply dbo.DelimitedSplit8K(s1.Item, '-') s3
 Where s2.Item = '86'
   And s3.ItemNumber = 1
1 Like