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
– Eray Balkanli, Commented Jul 22, 2021 at 20:09
Is this your answer? stackoverflow.com/questions/16667251/… — sa-es-ir
– sa-es-ir, Commented Jul 22, 2021 at 20:12

Eray Balkanli · Accepted Answer · 2021-07-22 20:14:18Z

1

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)

answered Jul 22, 2021 at 20:14

Eray Balkanli

8,04013 gold badges60 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1