Select Query to Remove Non-Numeric Characters

Remove all non numeric characters in sql SELECT

Some time ago I solved that problem using the below function

create function dbo.[fnrReplacetor](@strtext varchar(2000))
returns varchar(2000)
as
begin
    declare @i int = 32, @rplc varchar(1) = '';
    while @i < 256
    begin
        if (@i < 48 or @i > 57) and CHARINDEX(char(@i),@strtext) > 0
        begin
            --° #176 ~ 0   --¹ #185 ~ 1   --² #178 ~ 2   --³ #179 ~ 3
            set @rplc = case @i
            when 176 then '0'
            when 185 then '1'
            when 178 then '2'
            when 179 then '3'
            else '' end;

            set @strtext = REPLACE(@strtext,CHAR(@i),@rplc);
        end

        set @i = @i + 1;
    end
    return @strtext;
end

GO

select dbo.[fnrReplacetor]('12345/97')

Note it ill also consider characters °,¹,²,³ numeric and replace then with 0,1,2,3.

I put it in a function to readly reuse it in my scenario I needed to fix many columns in many tables at once.

update t
set t.myColumn = dbo.[fnrReplacetor](tempdb.myColumn)
 from test t
 where tempdb.myColumn is not null

or just

select dbo.[fnrReplacetor](tempdb.myColumn) as [Only Digits]
 from test t
where tempdb.myColumn is not null

Obs: this is not the fatest way but a thorough one.

Edit

A non UDF solution must be use REPLACE but since regex is not that great in SQL you can end doing something nasty like the below example:

declare @test as table (myColumn varchar(50))

insert into @test values ('123/45'),('123-4.5')

Select 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(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(myColumn,'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',''),'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',''),'.',''),'-',''),'/','')
from @test

Select query to remove non-numeric characters value and get top value in SQL

You can achieve by this

select 
Max(CONVERT(int, stuff(Round, 1, patindex('%[0-9]%', Round)-1, '')))
from Table_LKP_RoundInfo

Remove all non-numeric characters from a field

There isn't any "builtin" function that will do this operation in MySQL.

One option is to create your own stored function (if you have sufficient privileges on the database).

 DELIMITER $$

 DROP FUNCTION IF EXISTS `uf_only_digits`$$

 CREATE FUNCTION `uf_only_digits`(as_val VARCHAR(65535))
 RETURNS VARCHAR(65535)
 DETERMINISTIC
 BEGIN
   DECLARE retval VARCHAR(65535);
   DECLARE i INT;
   DECLARE strlen INT;
   -- shortcut exit for special cases
   IF as_val IS NULL OR as_val = '' THEN
     RETURN as_val;
   END IF;
   -- initialize for loop
   SET retval = '';
   SET i = 1;
   SET strlen = CHAR_LENGTH(as_val);
 do_loop:
   LOOP
     IF i > strlen THEN
       LEAVE do_loop;
     END IF;
     IF SUBSTR(as_val,i,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
       SET retval = CONCAT(retval,SUBSTR(as_val,i,1));
     END IF;
     SET i = i + 1;
   END LOOP do_loop;
   RETURN retval;
 END$$

 DELIMITER ;

And be sure to test this before you use it an UPDATE statement.

 SELECT t.foo
      , uf_only_digits(t.foo)
   FROM ( SELECT '' AS foo
          UNION ALL SELECT ' x'
          UNION ALL SELECT 'a1b2'
          UNION ALL SELECT '1-888-555-1212 ext 213'
          UNION ALL SELECT '1-800-FLOWERS'
        ) t

Returns:

 foo                     uf_only_digits(t.foo)  newlen  
 ----------------------  ---------------------  --------
                                                       0
  x                                                    0
 a1b2                    12                            2
 1-888-555-1212 ext 213  18885551212213               14
 1-800-FLOWERS           1800                          4

(The last two rows might give us pause to reconsider what we really want to achieve. If it were me, I would creating a new column, and saving the existing value in it, before I did an UPDATE.)

 -- new column same size as `phone` column
 ALTER TABLE mytable ADD COLUMN orig_phone VARCHAR(40) NULL 
   COMMENT 'original phone value, before update to all digits';

 UPDATE mytable t
    SET t.orig_phone = t.phone ;

 UPDATE mytable t
    SET t.phone = uf_only_digits(t.phone) ;

How to remove non-numeric characters (except full stop "." ) from a string in amazon redshift

Please try this:

The below regex_replace expression will replace all character which are not ("^") in the (range of 0-9) & "."

SELECT regexp_replace('ABC$$$%%11633123.60','([^0-9.])','') FROM DUAL;

It returns the expected output "11633123.60"

How to remove non-numeric characters from MySQL cells

Turns out the answer was the pseudo code that I wrote!

---

Related Topics