SQL: Extract numbers from string using Patindex-function

Question

string #1: King's road 8
string #2: Abbey road5
string #3: Carnaby Street 18-20a
string #5: //
string #5: Baker Str. 21a-21e

and split them up into:

colA:
King's road
Abbey road
Carnaby Street
//
Baker Str.

colB:
8
5
18-20a
NULL
21a-21e

I am grateful for any help!

So far I tried to split the street name from street number by using "SUBSTRING" and "PATINDEX" but I could not figure out how to accomplish it.

DROP TABLE IF EXISTS #Test
CREATE TABLE #Test
(
    A VARCHAR(MAX) 
)
INSERT INTO #Test VALUES ('King's road 8')
INSERT INTO #Test VALUES ('Abbey road5') -- no ws between number and road
INSERT INTO #Test VALUES ('Carnaby Street 18-20A')
INSERT INTO #Test VALUES ('//')          -- if '//" is met, return NULL
INSERT INTO #Test VALUES ('Baker Str. 21a-21e')


SELECT SUBSTRING --here the fun begins, just exemplary...
        (
        A, PATINDEX('%[0-9]%',A), PATINDEX('%[0-9] [^0-9]%',A + 'e') - PATINDEX('%[0-9]%', A) + 1
        ) AS Number 
FROM  #Test

What do you want to receive in the case of `999 Example Street, Cityville 22422`? — Andy, Aug 27 '19 at 18:01
This is not what SQL Server is for. Write a parser in a proper programming language. — Salman A, Aug 27 '19 at 18:44

score 0 · Accepted Answer · answered Aug 27 '19 at 18:08

According to your sample data and expected results, you can get it with some relatively simple expressions.

SELECT A,
    SUBSTRING( A, 0, ISNULL(NULLIF(PATINDEX('%[0-9]%', A), 0), LEN(A)+1)),
    SUBSTRING( A, NULLIF(PATINDEX('%[0-9]%', A), 0), LEN(A))
FROM  #Test;

For the first column, I start with 0 to avoid substracting 1 to the first digit position. I then use a combination of ISNULL(NULLIF(,0)LEN(A)) to assign the total length in case there are no digits available.

For the second columns, I still use NULLIF, but I don't use ISNULL to keep the value as NULL. I then just add the total length of the string.

Thanks @Luis Cazares! Thanks also for the explanation of the SQL query you wrote, this is what I was looking for! — DaveS, Aug 27 '19 at 20:05

SQL: Extract numbers from string using Patindex-function

1 Answers1