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.

Answer 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)))

Answer 2

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

Answer 3

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.