mysql汉字对应字段_mysql和SqlServer 中取得汉字字段的各汉字首字母

DELIMITER ;;

CREATE  FUNCTION `getPY`(in_string VARCHAR(65534)) RETURNS mediumtext CHARSET utf8

BEGIN

DECLARE tmp_str VARCHAR(65534) charset gbk DEFAULT '' ; #截取字符串，每次做截取后的字符串存放在该变量中，初始为函数参数in_string值

DECLARE tmp_len SMALLINT DEFAULT 0;#tmp_str的长度

DECLARE tmp_char VARCHAR(2) charset gbk DEFAULT '';#截取字符，每次 left(tmp_str,1) 返回值存放在该变量中

DECLARE tmp_rs VARCHAR(65534) charset gbk DEFAULT '';#结果字符串

DECLARE tmp_cc VARCHAR(2) charset gbk DEFAULT '';#拼音字符，存放单个汉字对应的拼音首字符

SET tmp_str = in_string;#初始化，将in_string赋给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最左端的首个字符，注意这里是获取首个字符，该字符可能是汉字，也可能不是。

SET tmp_cc = tmp_char;#左端首个字符赋值给拼音字符

IF LENGTH(tmp_char)>1 THEN#判断左端首个字符是多字节还是单字节字符，要是多字节则认为是汉字且作以下拼音获取，要是单字节则不处理。

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 ;

效果图：

--将中文字符串转化成文字首拼音的组合

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)

--如果非汉字字符，返回原字符

set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901

then (select top 1 PY from (

select 'A' as PY,N'骜' as word

union all select 'B',N'簿'

union all select 'C',N'错'

union all select 'D',N'鵽'

union all select 'E',N'樲'

union all select 'F',N'鳆'

union all select 'G',N'腂'

union all select 'H',N'夻'

union all select 'J',N'攈'

union all select 'K',N'穒'

union all select 'L',N'鱳'

union all select 'M',N'旀'

union all select 'N',N'桛'

union all select 'O',N'沤'

union all select 'P',N'曝'

union all select 'Q',N'囕'

union all select 'R',N'鶸'

union all select 'S',N'蜶'

union all select 'T',N'箨'

union all select 'W',N'鹜'

union all select 'X',N'鑂'

union all select 'Y',N'韵'

union all select 'Z',N'咗'

) T

where word>=@word 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

效果图

很实用的两个自定义函数，积累总是一点一点的。