如何在MySQL中替换非数字字符? [英] How to replace non-numeric characters in MySQL?
本文介绍了如何在MySQL中替换非数字字符?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想替换MySQL中VARCHAR字段中的所有非数字字符,因为它是一个电话表,在导入它们时,我发现了一些不应在其中的"Fax:xxx"或-"字符.
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.
我希望不涉及多个REPLACE()调用的解决方案,
I'd prefer a solution that not involves multiple REPLACE() calls,
预先感谢
推荐答案
这是一个mysql函数:
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
在更新查询中将其用作本机mysql函数.
Use it as a native mysql function in your update query.
这篇关于如何在MySQL中替换非数字字符?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文