MySQL全角转换为半角_MySQL

?
序言：
用户注册时候，录入了全角手机号码，所以导致短信系统根据手机字段发送短信失败，现在问题来了，如何把全角手机号码变成半角手机号码？

1，手机号码全角转换成半角
先查询出来全角半角都存在的手机号码的数据
SELECT a.username ,COUNT(1) AS num
FROM(
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(uu.user_name,’０’,’0′),’１’,’1′),’２’,’2′),’３’,’3′),’４’,’4′) ,’５’,’5′),’６’,’6′),’７’,’7′) ,’８’,’8′),’９’,’9′) AS username
FROM UC_USER uu WHERE uu.`USER_NAME` IS NOT NULL
)a GROUP BY a.username HAVING (COUNT(1)>1)
;
得到如下重复记录：
(“MB.134xx76802x” ,
“MB.136xx88105x” ,
“MB.152xx80801x” ,
“MB.157xx49518x” ,
“MB.186xx88282x” ,
“MB.189xx94855x” ); )

然后删除掉已经存在半角的全角手机号码记录，不然转换后会有重复的手机号码。
DELETE FROM `UC_USER`
WHERE MOBILE LIKE ‘%１%’
AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(user_name,’０’,’0′),’１’,’1′),’２’,’2′),’３’,’3′),’４’,’4′) ,’５’,’5′),’６’,’6′),’７’,’7′) ,’８’,’8′),’９’,’9′)
IN(“MB.134xx76802x” ,
“MB.136xx88105x” ,
“MB.152xx80801x” ,
“MB.157xx49518x” ,
“MB.186xx88282x” ,
“MB.189xx94855x” );

之后再修改全角手机号码为半角手机号码
UPDATE UC_USER uu
SET uu.`MOBILE`=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(uu.`MOBILE`,’０’,’0′),’１’,’1′),’２’,’2′),’３’,’3′),’４’,’4′) ,’５’,’5′),’６’,’6′),’７’,’7′) ,’８’,’8′),’９’,’9′),
uu.`USER_NAME`=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(uu.user_name,’０’,’0′),’１’,’1′),’２’,’2′),’３’,’3′),’４’,’4′) ,’５’,’5′),’６’,’6′),’７’,’7′) ,’８’,’8′),’９’,’9′)
WHERE uu.`MOBILE` IS NOT NULL;

2，如何把所以的全角转换成半角
上面只是人为用比较笨拙的10个replace将全角转换成了半角，有没有一种通用的思路或者方法来实现呢？于是google了很多资料，写下如下的存储函数。
DELIMITER $$
USE csdn $$
CREATE FUNCTION `csdn`.`func_convert`(p_str VARCHAR(200),flag INT)
RETURNS VARCHAR(200)
BEGIN
DECLARE pat VARCHAR(8);
DECLARE step INT ;
DECLARE i INT ;
DECLARE spc INT;
DECLARE str VARCHAR(200);

SET str=p_str;
IF flag=0 THEN /**全角换算半角*/
SET pat= N’%[！-～]%’ ;
SET step= -65248 ;
SET str = REPLACE(str,N’　’,N’ ‘);
ELSE /**半角换算全角*/
SET pat= N’%[!-~]%’ ;
SET step= 65248 ;
SET str= REPLACE(str,N’ ‘,N’　’) ;
END IF;

SET i=LOCATE(pat,str) ;
loop1:WHILE i>0 DO
/**开始将全角转换成半角*/
SET str= REPLACE(str, SUBSTRING(str,i,1), CHAR(UNICODE(SUBSTRING(str,i,1))+step));
SET i=INSTR(str,pat) ;
END WHILE loop1;
RETURN(str)
END $$
DELIMITER ;

3，google出来的sqlserver中的全角半角转换函数。
DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `test`.`u_convert`(@str NVARCHAR(4000),@flag BIT )
RETURNS NVARCHAR
BEGIN
DECLARE @pat NVARCHAR(8);
DECLARE @step INTEGER;
DECLARE @i INTEGER;
DECLARE @spc INTEGER;
IF @flag=0
BEGIN
SELECT N’%[！-～]%’ INTO @pat;
SELECT -65248 INTO @step;
SELECT REPLACE(@str,N’　’,N’ ‘) INTO @str;
END
ELSE
BEGIN
SELECT N’%[!-~]%’ INTO @pat;
SELECT 65248 INTO @step;
SELECT REPLACE(@str,N’ ‘,N’　’) INTO @str;
END
SELECT patindex(@pat COLLAT本文来源[email protected]搞@^&代*@码)网5E LATIN1_GENERAL_BIN,@str) INTO @i;
WHILE @i>0 DO
SELECT REPLACE(@str, SUBSTRING(@str,@i,1), NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step)) INTO @str;
SELECT patindex(@pat COLLATE LATIN1_GENERAL_BIN,@str) INTO @i;
END WHILE
RETURN(@str)
END $$
DELIMITER ;

—————————————————————————————————————-

原博客地址： http://blog.itpub.net/26230597/viewspace-1316445/
原作者：黄杉 (mchdba)
—————————————————————————————————————-

喜欢 (0)赏

[搞代码]

分享 (0)