SELECT only numeric part of string

Question

Hi @ all,

im forcing a problem where i have a alphanumeric string. I want to make a select where i only select the numeric part of the string. every string starts with letters. I dont want the letters in my select only the digits.

a way i can do that with a select statement?

 

Answer 1

Hi,

Can you provide some sample input

---

Thanks and regards, Rishabh

Answer 2

Hello,

try

select substring([col], PatIndex('%[0-9]%', [col], len([col])) as number from [tab];

Regards,

Klaus

Answer 3

Hi,

try

select substring([col], PatIndex('%[0-9]%', [col], len([col])) as number from [tab];

I think you haven't close the braces for patindex....

---

Thanks and regards, Rishabh

Answer 4

Yes, you are right. Correct is:

select substring([col], PatIndex('%[0-9]%', [col]), len([col])) as number from [tab];

Klaus

Answer 5

GREAT!! Thank you guys!

I had the same thing in a script a couple of months ago but could not find it :-)

 

Answer 6

Great answer!! :)

---

DVR

Answer 7

What if characters occur on both sides of the number?

E.g. 123abc456 ? The output would be abc456