x

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

more ▼

asked Apr 18 at 09:00 AM in Default

avatar image

BalaVenkat
1

Welcome aboard. :-)

Apr 19 at 02:16 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

A quick way is to use the delimited split function 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...

more ▼

answered Apr 19 at 02:16 PM

avatar image

ThomasRushton ♦♦
41.3k 20 50 53

(comments are locked)
10|1200 characters needed characters left

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 )

more ▼

answered Apr 18 at 11:51 AM

avatar image

Gazz
1k 3 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x212
x22
x14
x10

asked: Apr 18 at 09:00 AM

Seen: 43 times

Last Updated: Apr 19 at 02:16 PM

Copyright 2016 Redgate Software. Privacy Policy