CODEWITHSUNDEEP
sqlsql-serverstring
user256890
user256890

Reputation: 3424

String manipulation with SQL Server

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
...

Upvotes: 0

Views: 52

Answers (2)

Chanukya
Chanukya

Reputation: 5883

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

Upvotes: 1

Kinexus
Kinexus

Reputation: 12904

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

Upvotes: 1

Related Questions