question

If it is always going to be 3 digits then something simple like SUBSTRING would work DECLARE @item as varchar(MAX) SET @item = '123_234_234_345_34A_456' SELECT SUBSTRING(@ITEM,17,3) o/w you probably want to create a function - Or you could bodge it by nesting CHARINDEX. The example below should give you an idea of how to do it: DECLARE @item as varchar(MAX) SET @item = '123_234_234_345_34A_456' SELECT CHARINDEX('_',@item) SELECT CHARINDEX('_',@item,CHARINDEX('_',@item)+1) SELECT CHARINDEX('_',@item, CHARINDEX('_',@item,CHARINDEX('_',@item)+1) +1 )

A quick way is to use the [delimited split function][1] written by Jeff Moden on SQLServerCentral. If you've got that created, then the query is as simple as: DECLARE @testdata TABLE ( HyphenatedString VARCHAR(MAX)) ; INSERT INTO @testdata ( HyphenatedString ) VALUES ( '123_234_234_345_34A_456' ) ; SELECT * FROM @testdata AS t CROSS APPLY dbo.DelimitedSplit8K(t.HyphenatedString, '_') AS dsk WHERE dsk.ItemNumber = 5; (5, because you want the fifth string in the set). This method will work on many versions of SQL Server, but is limited to the size of input string it'll accept. If you've got SQL Server 2016, you could just use the in-built 'STRING_SPLIT' function, but there are no guarantees that data will be returned in the right order... [1]: http://www.sqlservercentral.com/articles/Tally+Table/72993/