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?

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

Solution 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 

Solution 3

Well if you don't want to create a function, you can just something like this:

cast(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(YOUR_COLUMN
,'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J','')
,'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T','')
,'U',''),'V',''),'W',''),'X',''),'Y',''),'Z',''),'$',''),',',''),' ','') as float) 

Solution 4

input table

if you have data like above in the image, then use the below query

select field_3 from table where PATINDEX('%[ ~`!@#$%^&*_()=+\|{};",<>/?a-z]%', field_3)=0

Results will be look like this

Result table

Solution 5

I think you're wanting VBA's Val() function. Easy enough to accomplish with IsNumeric()

create function Val 
(
    @text nvarchar(40)
) 
returns float
as begin
    -- emulate vba's val() function
    declare @result float 
    declare @tmp varchar(40)

    set @tmp = @text
    while isnumeric(@tmp) = 0 and len(@tmp)>0 begin
        set @tmp=left(@tmp,len(@tmp)-1)
    end
    set @result = cast(@tmp as float)

    return @result
end

View more solutions

Comments

[user990016]

I'm looking at your example and it gives a conversion to int error if the column does not contain any numbers. Any thoughts?

[user990016]

And, what if the field has no numbers?

[Steve can help]

This solution will haunt my dreams forever.

[dllhell]

disturbing indeed

[Sean]

@user990016 then add a where clause to filter those records out, or a case statement to return a default value for those records. So: WHERE fieldName LIKE '%[0-9]%' and CASE WHEN fieldName NOT LIKE '%[0-9]%' THEN 'DEFAULT_VALUE' ELSE SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName)) END

[SteveCav]

Ah, the heady days of LISP.

[Nathan Smiechowski]

How can I unsee this?

[Reuel Ribeiro]

It is beautiful yet ugly at the same time 😂. But seriously, don't use it for real production code for both performance and for readability reasons.

[Borjovsky]

"I have no respect for those who have no respect for logic." - Arnold Schwarzenegger

[Nitin Deb]

What if the string starts with numbers and then characters and then again numbers? For example: '123abc$%10xyz9'. How can I get '123109' as the result with a select statement?