SQL query/function to extract the first number from a string? not just the 1st numerical digit but the entire number until we hit a non-numeric value

Question

I have two input strings that look like 'London 350 Paris 456 eu iu' and 'New York 154 Seattle 890 pc appl iu'.

Now, I need the first number from the string. So the query/function needs to loop through the entire string and get all numbers from the first time it sees a number and the first time it hits a non-number.

So in this case, the outputs should be '350' and '154' respectively.

I see a bunch of patindex answers. I probably would of went for a split string on spaces and take the first one that passes isnumeric(). Only posting as a possible alternative. — KeithL, Nov 25 '19 at 16:50
@KeithL [`IsNumeric()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql) is notoriously [problematic](http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/). — HABO, Nov 25 '19 at 19:15

score 1 · Answer 1 · answered Nov 25 '19 at 15:55

1

Use Patindex and substring

declare @str varchar(100) = 'London 350 Paris 456 eu iu'

select left(partialString,patindex('%[a-z]%',partialString)-1) 
    from (select partialString = substring(@str, patindex('%[0-9]%',@str), len(@str)))a

answered Nov 25 '19 at 15:55

Pரதீப்

91,748
19
131
172

score 1 · Accepted Answer · answered Nov 25 '19 at 15:56

1

In this way you can get the First number.

DECLARE @VAR VARCHAR(MAX) = 'London 350 Paris 456 eu iu'

SELECT SUBSTRING(@VAR, PATINDEX('%[0-9]%', @VAR), PATINDEX('%[^0-9]%', SUBSTRING(@VAR, PATINDEX('%[0-9]%', @VAR), 100)))

answered Nov 25 '19 at 15:56

Shakeer Mirza

5,054
2
18
41

score 0 · Answer 3 · answered Nov 25 '19 at 15:57

String manipulation in SQL Server is tricky. Here is one method:

select t.str, left(v.str, patindex('%[^0-9]%', v.str + ' '))
from (values ('London 350 Paris 456 eu iu')) t(str) cross apply
     (values(stuff(t.str, 1, patindex('%[0-9]%', t.str + '0') - 1, ''))) v(str);

Here is a db<>fiddle.

SQL query/function to extract the first number from a string? not just the 1st numerical digit but the entire number until we hit a non-numeric value

3 Answers3