From each string, extract numbers following a '#' and create separate row

Question

Post reply

From each string, extract numbers following a '#' and create separate row

SqlRookie

SSC Enthusiast

Points: 198
More actions
October 24, 2024 at 11:02 am

#4473138

Hi there,
This was posted in the wrong forum, hence duplicating here. I have SQL version 2019 (thanks to Phil, Scott and Steve for their previous inputs).
I've tried CROSS APPLY, PATINDEX and many other functions, but can't nail this.
For each record, I want to extract all numbers which follow a '#' and then create a new row for each.
Example String 1: "Hello world. #1234 has been replaced by #014521"
To return:
1234
014521
Example String 2: "#687459"
To return:
687459
If there is no '#', then return blank.
Thanks in advance. 🙂

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

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

Alan Burstein SSC Guru Points: 61138 More actions · Answer 1

Post got submitted twice. Sorry.

This reply was modified 3 days, 21 hours ago by Alan Burstein. Reason: Dupe

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61138 More actions · Answer 2

For this you can use a "splitter" (AKA "tokenizer" function.)

DECLARE @String VARCHAR(8000) = 'Hello world. #1234 has been replaced by #014521';

SELECT SomeNbr = REPLACE(split.[value],'#','')
FROM STRING_SPLIT(@String,' ') AS split
WHERE split.[value] LIKE '#[0-9]%';

Returns:

SomeNbr
----------
1234
014521

Against a table it would look like this:

DECLARE @Strings TABLE (StringID INT IDENTITY, SomeString VARCHAR(1000));
INSERT @Strings(SomeString)
VALUES 
('Hello world. #1234 has been replaced by #014521'),
('Numbers, numbers... #012 #999 #Numbers'),
('The quick brown fox...'),
('#000 Another row... #123') ;

SELECT
StringID = s.StringID,
SomeNbr = REPLACE(split.[value],'#','')
FROM @Strings AS s
CROSS APPLY STRING_SPLIT(s.SomeString,' ') AS split
WHERE split.[value] LIKE '#[0-9]%';

This returns:

StringID    SomeNbr
----------- -----------
1           1234
1           014521
2           012
2           999
4           000
4           123

This gets us what we need except for "If there is no '#', then return blank." For the blanks we'll need to push our logic into a subquery and leverage OUTER APPLY like so:

SELECT
  StringID = s.StringID,
  SomeNbr = ISNULL(split.SomeNbr,'')
FROM @Strings AS s
OUTER APPLY
(
  SELECT SomeNbr = REPLACE(split.[value],'#','')
  FROM STRING_SPLIT(s.SomeString,' ') AS split
  WHERE split.[value] LIKE '#[0-9]%'
) AS split;

Returns:

StringID   SomeNbr
----------- -----------
1           1234
1           014521
2           012
2           999
4           000
4           123

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

SqlRookie SSC Enthusiast Points: 198 More actions · Answer 3

Hi Alan,

Many thanks for the code you have provided. I am still evaluating how to apply this to an existing table, but I do have an observation. If there is no space between the number and '#', I get this:-

DECLARE @String VARCHAR(8000) = 'Hello world. #1234 has been replaced by #014521#2345';

SELECT SomeNbr = REPLACE(split.[value],'#','')
FROM STRING_SPLIT(@String,' ') AS split
WHERE split.[value] LIKE '#[0-9]%';

Result:

1234
0145212345

Should be:

1234

014521

2345

Also, if I declare this string with parenthesis around a number, it give me just the first occurrence of a #, 1234:-

DECLARE @String VARCHAR(8000) = 'Hello world. #1234 has been replaced by (#014521) (#2345)

Result:

1234

Thank you again! 🙂

p.s. How do I avoid the extra spaces between each line of text? lol

This reply was modified 3 days, 5 hours ago by SqlRookie.
This reply was modified 3 days, 4 hours ago by SqlRookie.

Steve Collins SSCrazy Points: 2144 More actions · Answer 4

Or what's the question? It seems the topic's scope has crept after solutions were posted

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

PangyuSong Grasshopper Points: 14 More actions · Answer 5

PangyuSong

Grasshopper

Points: 14

October 25, 2024 at 10:56 pm

#4473878

It's too difficult to answer. I am a new learner.

PangyuSong Grasshopper Points: 14 More actions · Answer 6

PangyuSong

Grasshopper

Points: 14

October 25, 2024 at 10:57 pm

#4473879

It is too difficult for me.