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