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

Answer 1

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.