MySQL full-angle conversion to half-angle, mysql full-angle conversion to half-Angle

MySQL full-angle conversion to half-angle, mysql full-angle conversion to half-Angle


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 $
CREATEFUNCTION 'csdn '. 'func _ convert' (p_str VARCHAR (200), flag INT)
RETURNSVARCHAR (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;


Bytes ----------------------------------------------------------------------------------------------------------------
<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.itpub.net/26230597/viewspace-1316445/
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------


How to convert the halfwidth and fullwidth

Last key + Space key
That is
Shift + Space

How to convert word punctuation across half width

Use the replacement function to replace the full-width punctuation with the half-width punctuation.