Extract only numeric values from String column

Question

I have a column of values that are as shown below:

ID
x-644478134
x-439220334
x-645948923
x-10686843432
x-4273883234

I would like to return a column like so:

ID
644478134
439220334
645948923
10686843432
4273883234

Can someone advise how to do this cleanly? I believe it is something to do with substring but not sure exactly

SELECT SUBSTRING(d.ID)
FROM data d

Always starts with "x-" ??? If so, try: Select substring(ID,3,LEN(ID)-2) — Eray Balkanli, Jul 22 '21 at 20:09
Is this your answer? https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string — sa-es-ir, Jul 22 '21 at 20:12

score 1 · Accepted Answer · answered Jul 22 '21 at 20:14

You need to use substring, charindex and len functions such as below, assuming the dash (-) is the separator of the text and numeric part:

declare @x varchar(50) = 'a-0123'
select substring(@x,CHARINDEX('-',@x,1)+1,len(@x)-CHARINDEX('-',@x,1))

If you are sure that ID always starts with x-, then:

Select substring(ID,3,LEN(ID)-2)

1 Answers1