POSTGRESQL获得汉字首字拼音函数(转)

最新推荐文章于 2023-08-23 11:37:19 发布

weixin_30388677

最新推荐文章于 2023-08-23 11:37:19 发布

阅读量466

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/wangbin/archive/2009/03/20/1417503.html

本文介绍了一个PostgreSQL的自定义函数，该函数能够将输入的中文字符串转换为对应的汉语拼音。通过一系列预设的拼音映射，此函数实现了对中文字符的有效转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE FUNCTION func_chinese_spell(str VARCHAR(2000)) RETURNS VARCHAR(2000) AS $$
DECLARE
word NCHAR(1);
code VARCHAR(2000);
i INTEGER;
chnstr VARCHAR(2000);
BEGIN
code := '';
i := 1;
chnstr := str;
WHILE LENGTH(chnstr)>0 LOOP
word := SUBSTRING(str,i,1);
code := code || CASE WHEN (ASCII(word) BETWEEN 19968 AND 19968+20901) THEN
(
SELECT p FROM
(
SELECT 'A' as p,N'驁' as w
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 w>=word ORDER BY p ASC LIMIT 1
)
ELSE word END;
i := i + 1;
chnstr := SUBSTRING(str,i,LENGTH(str)-i + 1);
END LOOP;

RETURN code;
END;
$$LANGUAGE plpgsql;