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:

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

Yep, patindex, assuming all numerics are grouped together, as in your example, else you might need a function. — Sean, Jul 04 '12 at 17:07
Do you have to do this in SQL or is post-query manipulation allowed? If so then I would use a regex to extract the number for display; I did a cursory search and did not come across anything that show that you can use a regular expression between SELECT and FROM. — Mark Sholund, Jul 04 '12 at 17:13
@SelectDistinct - What is missing in my answer ? Did you test the accepted post with below code `DECLARE @a VARCHAR(1212) = 'eded123wsws' SELECT SUBSTRING(@a, PATINDEX('%[0-9]%', @a), LEN(@a))` — , Jul 15 '12 at 06:18

score 30 · Accepted Answer · answered Jul 04 '12 at 17:11

30

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.

answered Jul 04 '12 at 17:11

Sean

14,359
13
74
124

2

And, what if the field has no numbers? – user990016 Oct 10 '16 at 23:48
1

@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` – Sean Jan 29 '18 at 18:38
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? – Nitin Deb Apr 27 '21 at 19:20

score 6 · Answer 2 · answered Jul 04 '12 at 18:06

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

score 4 · Answer 3 · 2012-07-04T17:30:52.757

DECLARE @NonNumeric varchar(1000) = 'RGI000Testing1000'
DECLARE @Index int  
SET @Index = 0  
while 1=1  
begin  
    set @Index = patindex('%[^0-9]%',@NonNumeric)  
    if @Index <> 0  
    begin  
        SET @NonNumeric = replace(@NonNumeric,substring(@NonNumeric,@Index, 1), '')  
    end  
    else    
        break;   
end     
select @NonNumeric -- 0001000

score 3 · Answer 4 · edited Apr 07 '16 at 18:56

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)

edited Apr 07 '16 at 18:56

Undo

25,519
37
106
129

answered Apr 07 '16 at 18:52

fuhry

79
1

30

This solution will haunt my dreams forever. – Steve can help Jul 31 '17 at 14:29
2

disturbing indeed – dllhell Oct 16 '17 at 13:22
3

Ah, the heady days of LISP. – SteveCav Apr 03 '19 at 03:08
4

How can I unsee this? – Tanveer Badar Jun 13 '19 at 13:14
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. – Reuel Ribeiro Aug 16 '19 at 04:53
"I have no respect for those who have no respect for logic." - Arnold Schwarzenegger – Borjovsky Apr 27 '21 at 11:44

score 2 · Answer 5 · answered Jul 05 '12 at 11:03

2

select substring(
                'jaksm234234',
                patindex('%[0-9]%','jaksm234234'),
                LEN('jaksm234234')-patindex('%[0-9]%','jaksm234234')+2
                )

answered Jul 05 '12 at 11:03

general exception

4,202
9
54
82

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 Oct 10 '16 at 23:43

score 2 · Answer 6 · answered Dec 20 '16 at 12:13

2

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

answered Dec 20 '16 at 12:13

Muhammad saqib

31
3

score 1 · Answer 7 · answered Jul 04 '12 at 17:09

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

kuklei · Answer 8 · 2018-03-17T15:06:53.843

Extract only numbers from a string. Returns a string with all the numbers inside. Example: this1is2one345long6789number will return 123456789

CREATE FUNCTION [dbo].[GetOnlyNumbers] (@Temp VARCHAR(1000))

RETURNS VARCHAR (1000) AS BEGIN

    DECLARE @KeepValues AS VARCHAR(50)
    SET @KeepValues = '%[^0-9]%'
    WHILE PATINDEX(@KeepValues, @Temp) > 0
        SET @Temp = STUFF(@Temp, PATINDEX(@KeepValues, @Temp), 1, '')

    RETURN @Temp
END

LukStorms · Answer 9 · 2021-12-10T04:41:38.057

0

A right with patindex for the reverse string works also for those

SELECT [Column], 
  CAST(RIGHT([Column], PATINDEX('%[0-9][^0-9]%', REVERSE([Column])+' ')) AS INT) as [Num]
FROM (VALUES 
('abd738927'),
('jaksm234234'),
('hfk342'),
('ndma0834'),
('jon99322'),
) val([Column])

Column	Num
abd738927	738927
jaksm234234	234234
hfk342	342
ndma0834	834
jon99322	99322

edited Dec 10 '21 at 04:41

answered Dec 10 '21 at 04:16

LukStorms

28,916
5
31
45

SQL take just the numeric values from a varchar

9 Answers9

Linked

Related