How to obtain the first letter of each Chinese character field in mysql, mysql Chinese Character

How to obtain the first letter of each Chinese character field in mysql, mysql Chinese Character

Drop function if exists 'getpy'; DELIMITER; create function 'getpy' (in_string VARCHAR (65534) RETURNS mediumtext CHARSET utf8 begin declare tmp_str VARCHAR (65534) charset gbk DEFAULT ''; # truncate string. The string after each truncate is stored in this variable. The initial value is the in_string value of the function parameter DECLARE tmp_len smallint default 0; # length of tmp_str DECLARE tmp_char VARCHAR (2) charset gbk DEFAULT ''; # truncate character. The returned values of left (tmp_str, 1) are stored in the variable DECLARE tmp_rs VARCHAR (65534) char. Set gbk DEFAULT ''; # result string DECLARE tmp_cc VARCHAR (2) charset gbk DEFAULT''; # pinyin character, which stores the first character SET tmp_str = in_string corresponding to a single Chinese character; # initialize and assign in_string to tmp_str SET tmp_len = LENGTH (tmp_str ); # initialization length WHILE tmp_len> 0 DO # enter this while SET tmp_char = LEFT (tmp_str, 1) If the calculated tmp_str length is greater than 0 ); # obtain the first character at the leftmost end of tmp_str. Note that this character is the first character. It may be a Chinese character or not. SET tmp_cc = tmp_char; # assign the first character on the left to the pinyin character if length (tmp_char)> 1 THEN # determine whether the first character on the left is multi-byte or single-byte, if it is multi-byte, it is considered as a Chinese character and obtained in the following pinyin format. If it is single-byte, It is not processed. Select elt (INTERVAL (CONV (HEX (tmp_char), 16,10), 0xB0A1, 0xB0C5, 0xB2C1, 0xB4EE, 0xB6EA, 0xB7A2, 0xB8C1, primary, 0xBBF7, primary, 0xC0AC, primary, 0xC4C3, 0xC5B6, 0xC5BE, 0xC6DA, 0xC8BB, 0xC8F6, 0 xCBFA, 0 xCDDA, 0xCEF4, 0xD1B9, 0xD4D1), 'A', 'B', 'C ', 'D', 'E', 'F', 'G', 'h', 'J', 'k', 'l', 'M', 'n ', 'o', 'P', 'Q', 'R', 's', 't', 'w', 'x', 'y', 'z ') INTO tmp_cc; # Get the first character of Chinese pinyin end if; SET tmp_rs = CONCAT (tmp_rs, tmp_cc ); # concatenate the first character of the left end of the current tmp_str pinyin string with the return string SET tmp_str = SUBSTRING (tmp_str, 2 ); # Remove the first character from the left END of tmp_str from SET tmp_len = LENGTH (tmp_str); # Calculate the LENGTH of the current string end while; RETURN tmp_rs; # RETURN the string END; DELIMITER;

The mysql database already has Chinese characters. What statements can I use to call the data content by the first letter in a fuzzy query?

The usage of Like is mainly used in fuzzy search, and most query strings are used. Here, the general usage of Like is as follows:
Example 1: the query name field contains the word "Ming.

Select * from table1 where name like '% Ming %'

Do not use * here. It is recommended to use % when using a string consisting of 0 or any character.

However, you can replace each other at the beginning or end. If you use both at the beginning and end, you must use %

Example 2: The name field starts with "Li.

Select * from table1 where name like 'Lee *'

Or

Select * from table1 where name like 'Li %'

Example 3: the query name field contains numbers.

Select * from table1 where name like '% [0-9] %'

Example 4: the query name field contains lowercase letters.

Select * from table1 where name like '% [a-z] %'

Example 5: the query name field does not contain numbers.

Select * from table1 where name like '% [! 0-9] %'

To add one ,? Or _ represents a single character

In Excel, how does one automatically extract the first Chinese pinyin Letter of the field?

= LOOKUP (CODE (LEFT (B1, 1), 45217 + {, 544,1101, clerk, 2080,2560, 2902,3845, clerk, 4679, clerk, 5405,5689, 6170,6229, 7001,7481, clerk, clerk, 9264 },{ "A", "B", "C", "D", "E", "F", "G", "H", "J ", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T ", "W", "X", "Y", "Z "})

Yes