October 24, 2024 at 11:02 am
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. 🙂
October 24, 2024 at 5:36 pm
Post got submitted twice. Sorry.
-- Itzik Ben-Gan 2001
October 24, 2024 at 5:36 pm
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
-- Itzik Ben-Gan 2001
October 25, 2024 at 9:45 am
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
October 25, 2024 at 7:47 pm
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
October 25, 2024 at 10:56 pm
It's too difficult to answer. I am a new learner.
October 25, 2024 at 10:57 pm
It is too difficult for me.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply