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','0'),'1','1'),'2','2'),'3','3'),'4','4') ,'5','5'),'6','6'),'7','7') ,'8','8'),'9','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 '%1%'
AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(user_name,'0','0'),'1','1'),'2','2'),'3','3'),'4','4') ,'5','5'),'6','6'),'7','7') ,'8','8'),'9','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','0'),'1','1'),'2','2'),'3','3'),'4','4') ,'5','5'),'6','6'),'7','7') ,'8','8'),'9','9'),
uu.`USER_NAME`=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(uu.user_name,'0','0'),'1','1'),'2','2'),'3','3'),'4','4') ,'5','5'),'6','6'),'7','7') ,'8','8'),'9','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 ;
--------------------------------------分割線 --------------------------------------
Ubuntu 14.04下安裝MySQL
《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF
Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主從伺服器
Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集
Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二進位安裝
--------------------------------------分割線 --------------------------------------
本文永久更新連結地址: