String manipulation with SQL Server

Question

I have the following table with (man-made) IDs.

ID       Name
AB12345  John
12346    Charles
...

How do I write a SELECT that returns only the number segment of the ID column? Like this:

ID       Name
12345    John
12346    Charles
...

make a scalar function the loops through each character and concatenates character to string value only if it is BETWEEN '0' AND '9' — Cato, Oct 19 '16 at 08:43

score 1 · Answer 1 · edited May 23 '17 at 12:17

1

You could write a regex to extract the numeric values that you are looking for.

This might help

Query to get only numbers from a string

edited May 23 '17 at 12:17

Community

1
1

answered Oct 19 '16 at 08:41

ChrisBint

12,773
6
40
62

score 1 · Accepted Answer · answered Oct 19 '16 at 08:44

1

SELECT SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-9]%', string + 't') - PATINDEX('%[0-9]%', 
                    string) + 1) AS Number
FROM table

here string is equal to id

answered Oct 19 '16 at 08:44

Chanukya

5,833
1
22
36

String manipulation with SQL Server

2 Answers2