Extracting string between certain characters

Question

Post reply

Extracting string between certain characters

vecerda

SSCrazy

Points: 2532
More actions
August 18, 2014 at 6:59 am

#294555

Hello,
I need help extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest]
CREATE TABLE [dbo].[StoreNumberTest](
[StoreNumber] [varchar](50) NULL,
[StoreNumberParsed] [varchar](50) NULL)
INSERT INTO [dbo].[StoreNumberTest]
([StoreNumber]
,[StoreNumberParsed])
VALUES
('USA-MW-AA-KY-117', NULL)
,('USA-W-AA-CA-132', NULL)
,('USA-MW-AA-OH-174', NULL)
,('USA-MA-AA-MD-163', NULL)
,('USA-SE-AA-FL-191-1-IL', NULL)
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117
CA132
OH174
MD163
FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement. Any help will be greatly appreciated.
Petr

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

It's pretty easy to accomplish using some well established methods.

First you can split the data using the "-" as a delimiter using Jeff Modens splitter function[/url].

Then you can concatenate the terms you need using the XML method for concatenating row values[/url].

All very fast and very set-based, without any loops, cursors or recursion.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoreNumberTest]') AND type IN (N'U'))

DROP TABLE [dbo].[StoreNumberTest];

GO

CREATE TABLE [dbo].[StoreNumberTest](

[StoreNumber] [varchar](50) NULL,

[StoreNumberParsed] [varchar](50) NULL);

INSERT INTO [dbo].[StoreNumberTest]

([StoreNumber]

,[StoreNumberParsed])

VALUES

('USA-MW-AA-KY-117', NULL)

,('USA-W-AA-CA-132', NULL)

,('USA-MW-AA-OH-174', NULL)

,('USA-MA-AA-MD-163', NULL)

,('USA-SE-AA-FL-191-1-IL', NULL);

WITH CTE_Splitted AS

(

SELECT

[StoreNumber], split.ItemNumber, Item = split.Item

FROM [dbo].[StoreNumberTest] test

CROSS APPLY dbo.DelimitedSplit8k(test.[StoreNumber],'-') split

WHERE split.ItemNumber IN (4,5)

)

SELECT

[StoreNumber]

,[StoreNumberParsed] = (SELECT Item + '' FROM CTE_Splitted c2

WHERE c1.StoreNumber = c2.StoreNumber

ORDER BY ItemNumber

FOR XML PATH(''))

FROM CTE_Splitted c1

GROUP BY [StoreNumber];

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

vecerda SSCrazy Points: 2532 More actions · Answer 2

OK, this looks great. I see the logic. What is the code behind dbo.DelimitedSplit8k?

thanks,

Petr

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

vecerda (8/18/2014)
OK, this looks great. I see the logic. What is the code behind dbo.DelimitedSplit8k?
thanks,
Petr

Take a look at the article of Jeff Moden I linked to.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

vecerda SSCrazy Points: 2532 More actions · Answer 4

Ooops, sorry, I did not scroll all the way down. Once I created the function, everything works awesome.

thanks,

Petr

Phil Parkin SSC Guru Points: 246961 More actions · Answer 5

I think this might also work. A bit of a hack though - the split is much more elegant.

select replace(substring(storeNumber,10,7),'-','')

from dbo.StoreNumberTest

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin