question

BalaVenkat avatar image
BalaVenkat asked

SUBSTRING and CHARINDEX SQL SERVER

Hi My First post here.! I would like to get the String which is between the Nth Hypens of the given string. Ex: 123_234_234_345_34A_456 The hypen count may vary, but I would like to get exactly the string within the 4th and 5th Hypen i.e as per example we want 34A .! Will be helpful if someone can guide me to achieve this.! Trying to achieve it in SQL Server. Regards, BalaV
sql serversubstringcharindexdelimited-string
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Welcome aboard. :-)
1 Like 1 ·
Gazz avatar image
Gazz answered
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 )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.