MySQL full-width conversion to half-width

MySQL full-width conversion to half-width

Preface:

When a user entered a fullwidth mobile phone number during registration, the text message system failed to send the text message based on the mobile phone field. Now the question is, how can I change the fullwidth mobile phone number to a halfwidth mobile phone number?

1. Convert the full-width mobile phone number to half-width
First, the data of the mobile phone numbers that exist in all corners
SELECT a. username, COUNT (1) AS num
FROM (
Select 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)
;
The following duplicate records are obtained:
("MB.134xx76802x ",
"MB.136xx88105x ",
"MB.152xx80801x ",
"MB.157xx49518x ",
"MB.186xx88282x ",
"MB.189xx94855x ");)

Delete the full-width mobile phone number record that already exists. Otherwise, there will be duplicate mobile phone numbers after conversion.
Delete from 'uc _ user'
Where mobile like '% 100'
And 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 ");

Then change the full-width mobile phone number to the half-width mobile phone number.
UPDATE UC_USER uu
SET uu. 'mobile' = 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 (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. How to convert the full angle to the half angle
The above is just a clumsy 10 replace to convert the full angle into a half angle. Is there a general idea or method to implement it? So google wrote a lot of information and wrote down the following storage functions.
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/** full width conversion half width */
SET pat = n' % [! -~] % ';
SET step =-65248;
SET str = REPLACE (str, n'', n '');
ELSE/** halfwidth conversion width */
SET pat = n' % [! -~] % ';
SET step = 65248;
SET str = REPLACE (str, n'', n '');
End if;

SET I = LOCATE (pat, str );
Loop1: WHILE I> 0 DO
/** Start to convert the fullwidth to halfwidth */
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. full-width half-width conversion function in google's 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;

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: