sql-serverstringt-sqlpatindex

T-SQL Extract Numbers from a string and everything in between

I'm using SQL Server 2014.

I understand how to extract numbers from a string using PATINDEX (Query to get only numbers from a string), however, how would I also include if they was actually a string value between the numbers that I also need to extract. e.g.;

This is my string:

15-19 Smith Street
1-3 Smith Street

And I need:

15-19
1-3

Also, if there is a suffix I need to extract that as a separate field:

147a York Road

I would need 147 as 1 field and the letter 'a' as another

How do I achive this in T-SQL?

Solution

  • You can use charindex() with patindex():

    select substring(col, patindex('%[0-9]%', col), 
                      len(col) - charindex(' ', col, patindex('%[0-9]%', col)) - 1
                ) as col