Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to replace non-numeric characters in MySQL?

Tags:

sql

mysql

I want to replace all non-numerical characters in a VARCHAR field in MySQL, because is a phone table and while importing them I found several "Fax:xxx" or "-" characters that shouldn't be there.

I'd prefer a solution that not involves multiple REPLACE() calls,

Thanks in advance

like image 693
Joaquín L. Robles Avatar asked Feb 28 '11 18:02

Joaquín L. Robles


People also ask

How change special characters MySQL?

The Replace() function is first choice. However, Special Characters can sometimes be tricky to write in a console. For those you can combine Replace with the Char() function. Ideally you could do a regex to find all the special chars, but apparently that's not possible with MySQL.


2 Answers

This is a mysql function:

delimiter //

create function IF NOT EXISTS LeaveNumber(str varchar(50)) returns varchar(50)
no sql
begin
declare verification varchar(50);
declare result varchar(50) default '';
declare character varchar(2);
declare i integer default 1;

if char_length(str) > 0 then
    while(i <= char_length(str)) do
        set character = substring(str,i,1);
        set verification = find_in_set(character,'1,2,3,4,5,6,7,8,9,0');

        if verification > 0 then
            set result = concat(result,character);
        end if;

        set i = i + 1;

    end while;

return result;
else
return '';
end if;
end //


delimiter ;

select leaveNumber('fAX:-12abcDE234'); -- RESULT: 12234

Use it as a native mysql function in your update query.

like image 86
Nicola Cossu Avatar answered Sep 30 '22 16:09

Nicola Cossu


Your best bet might be to use a regex replace UDF. I'd recommend you take a look at this one, which has a REGEX_REPLACE function that would probably suit your needs.

Your regex would probably just look like this:

[^0-9]
like image 39
Abe Miessler Avatar answered Sep 30 '22 15:09

Abe Miessler