The first letters of Chinese characters in MySQL and SQL Server

The first letter of each Chinese character field obtained in MySQL

This is reproduced in http://blog.csdn.net/lky5387/article/details/11973721

DELIMITER;;
CREATE FUNCTION ' getpy ' (in_string VARCHAR (65534)) RETURNS mediumtext CHARSET UTF8
BEGIN
DECLARE tmp_str VARCHAR (65534) charset GBK DEFAULT '; #截取字符串, each time the truncated string is stored in the variable, the initial function parameter in_string value
DECLARE Tmp_len SMALLINT DEFAULT 0; #tmp_str的长度
DECLARE Tmp_char VARCHAR (2) CharSet GBK default '; #截取字符, each left (tmp_str,1) return value is stored in the variable
DECLARE tmp_rs VARCHAR (65534) charset GBK DEFAULT '; #结果字符串
DECLARE tmp_cc VARCHAR (2) CharSet GBK DEFAULT '; #拼音字符, storing the phonetic first character corresponding to a single Chinese character
Set tmp_str = in_string; #初始化, assign in_string to Tmp_str
SET Tmp_len = LENGTH (TMP_STR); #初始化长度
While Tmp_len > 0 do #如果被计算的tmp_str长度大于0则进入该while
SET Tmp_char = left (tmp_str,1); #获取tmp_str最左端的首个字符, note that this is the first character to be obtained, which may or may not be a Chinese character.
SET tmp_cc = Tmp_char; #左端首个字符赋值给拼音字符
If LENGTH (Tmp_char) >1 then# determines whether the first character on the left side is multibyte or single-byte character, and if multibyte is considered a kanji and is obtained as the following pinyin, if the single byte is not processed.
SELECT ELT (INTERVAL (CONV (HEX (Tmp_char), 16,10), 0xb0a1,0xb0c5,0xb2c1,0xb4ee,0xb6ea,0xb7a2,0xb8c1,0xb9fe,0xbbf7, 0xbfa6,0xc0ac
, 0xc2e8,0xc4c3,0xc5b6,0xc5be,0xc6da,0xc8bb,0xc8f6,0xcbfa,0xcdda, 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; #获得汉字拼音首字符
END IF;
SET tmp_rs = CONCAT (TMP_RS,TMP_CC); #将当前tmp_str左端首个字符拼音首字符与返回字符串拼接
SET tmp_str = SUBSTRING (tmp_str,2); #将tmp_str左端首字符去除
SET Tmp_len = LENGTH (TMP_STR); #计算当前字符串长度
END while;
RETURN tmp_rs; #返回结果字符串
END;;
DELIMITER;

：

The first letter of the Chinese character field in SQL Server

--Convert Chinese strings into phonetic alphabet combinations
Create function dbo.fun_getpy (@str nvarchar (4000))
Returns nvarchar (4000)
As
Begin
DECLARE @word nchar (1), @PY nvarchar (4000)
Set @PY = ' '
While Len (@str) >0
Begin
Set @word =left (@str, 1)
--If non-kanji characters, return the original character
Set @[email protected]+ (case when Unicode (@word) between 19968 and 19968+20901
Then (the select top 1 PY from (
Select ' A ' as Py,n ' ao ' as word
UNION ALL SELECT ' B ', N ' book '
UNION ALL SELECT ' C ', N ' wrong '
UNION ALL SELECT ' D ', N ' 鵽 '
UNION ALL SELECT ' E ', N ' 樲 '
UNION ALL SELECT ' F ', N ' Leiognathi '
UNION ALL SELECT ' G ', N ' hiker '
UNION ALL SELECT ' H ', N ' inceѕt '
UNION ALL SELECT ' J ', N ' 攈 '
UNION ALL SELECT ' K ', N ' 穒 '
UNION ALL SELECT ' L ', N ' 鱳 '
UNION ALL SELECT ' M ', N ' temperature '
UNION ALL SELECT ' n ', n ' 桛 '
UNION ALL SELECT ' O ', N ' retting '
UNION ALL SELECT ' P ', N ' exposure '
UNION ALL SELECT ' Q ', N ' 囕 '
UNION ALL SELECT ' R ', N ' 鶸 '
UNION ALL SELECT ' S ', N ' 蜶 '
UNION ALL SELECT ' T ', N ' sheaths '
UNION ALL SELECT ' W ', N ' Mo '
UNION ALL SELECT ' X ', N ' 鑂 '
UNION ALL SELECT ' Y ', N ' rhyme '
UNION ALL SELECT ' Z ', N '
) T
where Word>[email protected] collate CHINESE_PRC_CS_AS_KS_WS
ORDER BY PY ASC) Else @word end)
Set @str =right (@str, Len (@str)-1)
End
Return @PY
End
GO

Very practical two custom functions, accumulation is always 1.1 points.