MySQL full-width conversion to half-width

Source: Internet
Author: User
Preface: when a user registers, the full-angle mobile phone number is entered. As a result, the text message system fails to send text messages based on the mobile phone field. Now the question is, how can we change the full-angle mobile phone number to half?

Preface: when a user registers, the full-angle mobile phone number is entered. As a result, the text message system fails to send text messages based on the mobile phone field. Now the question is, how can we change the full-angle mobile phone number to half?

Preface:

The user entered a full-width mobile phone number during registration, so 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 full-width mobile phone number to a half-width 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 --------------------------------------

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.