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

Question

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

SqlRookie

SSC Enthusiast

Points: 198
More actions
October 23, 2024 at 4:32 pm

#4472818

Hi there,
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 number, then leave blank.
Thanks in advance. 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

One possibility

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

SELECT STUFF (value, 1, 1, '')
FROM STRING_SPLIT(@x, ' ')
WHERE LEFT(value, 1) = '#';

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101123 More actions · Answer 2

An alternative (maybe slightly less overhead?, esp. for long strings):

SELECT LEFT(item, CHARINDEX(' ', item + ' ')) AS value
FROM dbo.DelimitedSplit8K (@x, '#') /*or STRING_SPLIT(), if available to you AND 
    the order of values doesn't have to be the same as the original data*/WHERE LEFT(item, 1) LIKE '[0-9]';

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Collins SSCrazy Points: 2174 More actions · Answer 3

SqlRookie wrote:

... extract all numbers which follow a '#' and then create a new row for each.

So far I'm not seeing a solution for which each character returned is an integer. Try this counterexample

DECLARE @x VARCHAR(500) = '#X12R #12X D34';

SELECT LEFT(item, CHARINDEX(' ', item + ' ')) AS value
FROM dbo.DelimitedSplit8K_Lead (@x, '#') /*or STRING_SPLIT(), if available to you AND 
    the order of values doesn't have to be the same as the original data*/WHERE LEFT(item, 1) LIKE '[0-9]';

SELECT STUFF (value, 1, 1, '')
FROM STRING_SPLIT(@x, ' ')
WHERE LEFT(value, 1) = '#';

Maybe something like this

select *
from (values ('#1234 has #1 been replaced by #014521'),
             (' #1234 has 555 been replaced by #014521'),
             ('#X12R #12X D34'),
             ('Hello world.  #X1234 has been replaced by #014#s521'),
             (' #123456')) v(test_string)
     cross apply string_split(v.test_string, '#', 1) ss
     cross apply (values (patindex('%[^0-9]%', ss.[value]))) ndx(non_integer)
     cross apply (values (iif(ndx.non_integer>0, left(ss.[value], ndx.non_integer-1), ss.[value]))) results(val)
where len(results.val)>0
order by v.test_string, ss.ordinal;

First, the code splits the input string on # using STRING_SPLIT and specifying the optional 3rd parameter so that an ordinal output column is generated. Next, PATINDEX is used to search the split values for the first non 0-9 integer by offset position. Last, if the non integer position is greater than 0 then substring to get the numerical characters using LEFT, otherwise return the whole split value

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

ScottPletcher SSC Guru Points: 101123 More actions · Answer 4

Good point. I was in too much of a hurry when I wrote the other code:

DECLARE @x varchar(500) = 'xxx.  #1234 has been replaced by #014521 #1a23 #23 #4c';

SELECT *
FROM dbo.DelimitedSplit8K (@x, '#') 
CROSS APPLY (
    SELECT LEFT(item, CHARINDEX(' ', item + ' ') - 1) AS value
) AS ca1
WHERE value NOT LIKE '%[^0-9]%'

:

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SqlRookie SSC Enthusiast Points: 198 More actions · Answer 5

Thank you Phil, Scott and Steve for your informative and concise replies! Much appreciated.

I'm struggling with one thing: dbo.DelimitedSplit8K. What is this?

Many thanks! 🙂

Footnote... Steve: I'm getting this error for the following SQL, "Procedure or function string_split has too many arguments specified"

select *
from (values ('#1234 has #1 been replaced by #014521'),
(' #1234 has 555 been replaced by #014521'),
('#X12R #12X D34'),
('Hello world. #X1234 has been replaced by #014#s521'),
(' #123456')) v(test_string)
cross apply string_split(v.test_string, '#', 1) ss
cross apply (values (patindex('%[^0-9]%', ss.[value]))) ndx(non_integer)
cross apply (values (iif(ndx.non_integer>0, left(ss.[value], ndx.non_integer-1), ss.[value]))) results(val)
where len(results.val)>0
order by v.test_string, ss.ordinal;

This reply was modified 2 months ago by SqlRookie.
This reply was modified 2 months ago by SqlRookie.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 6

Check this article for the DelimitedSplit8k function:

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function

We have assumed that you are using SQL Server 2022, as that is the forum you have posted in. The third argument of string_split() was introduced in 2022, so if you are using an earlier version (or your database has an earlier compatibility level) you will get the error you are seeing.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SqlRookie SSC Enthusiast Points: 198 More actions · Answer 7

I have SQL Server Management Studio v19.0.1 and 18.12.1 installed. Are these compatible?

Phil Parkin SSC Guru Points: 246986 More actions · Answer 8

That was not an SSMS error, it was an error generated by the server when attempting to execute your query. You can see your SQL Server version by running this:

SELECT
    Version = SERVERPROPERTY('productversion')
,   Level   = SERVERPROPERTY('productlevel')
,   Edition = SERVERPROPERTY('edition');

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SqlRookie SSC Enthusiast Points: 198 More actions · Answer 9

I see. This is what it's giving me:-

Version Level Edition

15.0.4261.1 RTM Enterprise Edition (64-bit)

Version Level Edition

15.0.4395.2 RTM Enterprise Edition (64-bit)

Many thanks Phil.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 10

That is SQL Server 2019, which explains the error message.

If you need to guarantee that your results are presented in the order in which they appeared in the original string, using DelimitedSplit8k is the way I would recommend.

This reply was modified 2 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SqlRookie SSC Enthusiast Points: 198 More actions · Answer 11

SqlRookie

SSC Enthusiast

Points: 198

October 24, 2024 at 10:47 am

#4473130

Thank you for clarifying Phil. 🙂

SqlRookie SSC Enthusiast Points: 198 More actions · Answer 12

Thank you all for your detailed feedback. I shall use this when we eventually get 2022!

Have posted this in 2019 forum.

Many thanks.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 13

SqlRookie wrote:

I'm struggling with one thing: dbo.DelimitedSplit8K. What is this?

You can get the code for it in the "Resources" section at the following article, which explains how it works, as well. It does what Microsoft finally made the STRING_SPLIT() function do 6 years after if first came out.

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)