SQL take just the numeric values from a varchar

Question

Say i have a few fields like the following:

abd738927
jaksm234234
hfk342
ndma0834
jon99322

Type: varchar.

How do I take just the numeric values from this to display:

738927
234234
342
0834
99322

Have tried substring however the data varies in length, and cast didnt work either due to being unable to convert, any ideas?

Answer 1

Here's the example with PATINDEX:

select SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))

This assumes (1) the field WILL have a numeric, (2) the numerics are all grouped together, and (3) the numerics don't have any subsequent characters after them.

Answer 2

Extract only numbers (without using while loop) and check each and every character to see if it is a number and extract it

   Declare @s varchar(100),@result varchar(100)
    set @s='as4khd0939sdf78' 
    set @result=''

    select
        @result=@result+
                case when number like '[0-9]' then number else '' end from 
        (
             select substring(@s,number,1) as number from 
            (
                select number from master..spt_values 
                where type='p' and number between 1 and len(@s)
            ) as t
        ) as t 
    select @result as only_numbers