How to replace non-numeric characters in MySQL?

Question

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

score 6 · Accepted Answer · edited May 17 '14 at 02:42

6

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.

edited May 17 '14 at 02:42

khaverim

3,386
5
36
46

answered Feb 28 '11 at 19:06

Nicola Cossu

54,599
15
92
98

Glad my function helped you. Thank you for upvoting. ;) – Nicola Cossu Jan 26 '12 at 23:17
Only the portugeese (?) variable names are very confusing, when trying to understand how it's done :D – Tomáš Fejfar Jan 27 '12 at 09:35
It's italian language. I'll rename them in English ASAP. :) – Nicola Cossu Jan 27 '12 at 15:03
AhAh, I always had trouble detecting the difference between spanish, portugeese and italian :)) Sry ... and thx for the rename :) – Tomáš Fejfar Jan 28 '12 at 12:07
I translated the variables to English. – khaverim May 17 '14 at 02:52
This works really well, thank you. However, it's best to avoid a term like `character` for a variable, as it's a reserved word. – Ricky McMaster Jan 18 '19 at 09:38

Abe Miessler · Answer 2 · 2011-02-28T19:07:54.510

1

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]

edited Feb 28 '11 at 19:07

answered Feb 28 '11 at 19:02

Abe Miessler

82,532
99
305
486

the link you provided is broken :( – Joaquín L. Robles Mar 01 '11 at 03:13

How to replace non-numeric characters in MySQL?

2 Answers2

Linked