sql函数汉字转拼音_SQL语句提取出中文的拼音首字母

‘壹’ sql语句如何实现将数据库表中某个字段的汉字批量转为拼音

sql可以实现但是你要准备建立一个庞大的字典库所有汉字拼音全存入然后写函数一个个比对返回
我都是用程序写的 sql实现比较蛋痛

‘贰’ SQL语句提取出中文的拼音首字母

正好最近收藏了一个你可以看下思路
--将中文字符串转化成文字首拼音的组合
create function 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
--函数调用实例：
select dbo.fun_getPY('中华人民共和国AAA01')

/*

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ZHRMGHGAAA01

(1 行受影响)

*/

‘叁’ sql 汉字转拼音，若有重复的在拼音加1

题主意思，表a已经有字段hz，现在要更新py这列？这样的话，只要update就行了，为什么说最好是函数实现呢？还是说题主想要selecthz,func(hz)froma这样的呢？

函数。

首先需要一个解析汉字到拼音的方法，这里有一个包，你可以复制过去直接执行。

脚本地址：

http://www.cnblogs.com/mellowsmile/p/4601288.html

附代码：

--不是我不贴，代码太多贴不上，题主还是去上面那个地址复制吧，注意复制全部。

在SQL窗口执行。

我这里建了一个测试表test，数据显示如下：

select a.* from test a;

题主，如此，可否？

‘肆’ 如何在sql 中将表中一整列的汉字全部变成拼音首字母

CREATE FUNCTION Fun_GetPY
(
@Str NVARCHAR(4000)
)

RETURNS NVARCHAR(4000)

AS

BEGIN
DECLARE @Word NCHAR(1)
DECLARE @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

调用这个函数就可以

‘伍’ SQL语句把用户名中文改成拼音字母

看看这个http://dhjdhja.blog.163.com/blog/static/64762009101684639346/ ps：奶奶的贴一个地址链接还不行什么破论坛，内容又发不完 --sql汉字转拼音
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOcreate function [dbo].[fn_GetPinyin](@words nvarchar(2000))
returns varchar(8000)
as
begin
declare @word nchar(1)
declare @pinyin varchar(8000)
declare @i int
declare @words_len int
declare @unicode int
set @i = 1
set @words = ltrim(rtrim(@words))
set @words_len = len(@words)
while (@i <= @words_len) --循环取字符
begin
set @word = substring(@words, @i, 1)
set @unicode = unicode(@word)
set @pinyin = ISNULL(@pinyin+space(1),'')+
(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 'ai',N'靉'
union all select 'an',N'黯'
union all select 'ang',N'醠'
union all select 'ao',N'骜'
union all select 'ba',N'欛'
union all select '',N'瓸' --韛兡瓸
union all select 'ban',N'瓣'
union all select 'bang',N'镑'
union all select 'bao',N'鑤'
union all select 'bei',N'鐾'
union all select 'ben',N'輽'
union all select 'beng',N'镚'
union all select 'bi',N'鼊'
union all select 'bian',N'变'
union all select 'biao',N'鳔'
union all select 'bie',N'彆'
union all select 'bin',N'鬓'
union all select 'bing',N'靐'
union all select 'bo',N'卜'
union all select 'bu',N'簿'
union all select 'ca',N'囃'
union all select 'cai',N'乲' --縩乲
union all select 'can',N'爘'
union all select 'cang',N'賶'
union all select 'cao',N'鼜'
union all select 'ce',N'簎'
union all select 'cen',N'笒'
union all select 'ceng',N'乽' --硛硳岾猠乽
union all select 'cha',N'诧'
union all select 'chai',N'囆'
union all select 'chan',N'颤'
union all select 'chang',N'韔'
union all select 'chao',N'觘'
union all select 'che',N'爡'
union all select 'chen',N'谶'
union all select 'cheng',N'秤'
union all select 'chi',N'鷘'
union all select 'chong',N'铳'
union all select 'chou',N'殠'
union all select 'chu',N'矗'
union all select 'chuai',N'踹'
union all select 'chuan',N'鶨'
union all select 'chuang',N'怆'
union all select 'chui',N'顀'
union all select 'chun',N'蠢'
union all select 'chuo',N'縒'
union all select 'ci',N'嗭' --赐嗭
union all select 'cong',N'謥'
union all select 'cou',N'辏'
union all select 'cu',N'顣'
union all select 'cuan',N'爨'
union all select 'cui',N'臎'
union all select 'cun',N'籿'
union all select 'cuo',N'错'
union all select 'da',N'橽'
union all select 'dai',N'靆'
union all select 'dan',N'饏'
union all select 'dang',N'闣'
union all select '',N'纛'
union all select 'de',N'的'
union all select 'den',N'扽'
union all select 'deng',N'镫'
union all select 'di',N'䗖'
union all select 'dia',N'嗲'
union all select 'dian',N'驔'
union all select 'diao',N'鑃'
union all select 'die',N'嚸' --眰嚸
union all select 'ding',N'顁'
union all select 'diu',N'铥'
union all select 'dong',N'霘'
union all select 'dou',N'鬭'
union all select '',N'蠹'
union all select 'an',N'叾' --簖叾
union all select 'i',N'譵'
union all select 'n',N'踲'
union all select 'o',N'鵽'
union all select 'e',N'鳄'
union all select 'en',N'摁'
union all select 'eng',N'鞥'
union all select 'er',N'樲'
union all select 'fa',N'发'
union all select 'fan',N'瀪'
union all select 'fang',N'放'
union all select 'fei',N'靅'
union all select 'fen',N'鲼'
union all select 'feng',N'覅'
union all select 'fo',N'梻'
union all select 'fou',N'鴀'
union all select 'fu',N'猤' --鳆猤
union all select 'ga',N'魀'
union all select 'gai',N'瓂'
union all select 'gan',N'灨'
union all select 'gang',N'戆'
union all select 'gao',N'锆'
union all select 'ge',N'獦'
union all select 'gei',N'给'
union all select 'gen',N'搄'
union all select 'geng',N'堩' --亘堩啹喼𠮶
union all select 'gong',N'兣' --熕贑兝兣
union all select 'gou',N'购'
union all select 'gu',N'顾'
union all select 'gua',N'诖'
union all select 'guai',N'恠'
union all select 'guan',N'鱹'
union all select 'guang',N'撗'
union all select 'gui',N'鱥'
union all select 'gun',N'謴'
union all select 'guo',N'腂'
union all select 'ha',N'哈'
union all select 'hai',N'饚'

‘陆’ 数据库oracle（plsql）怎么把汉字转化成拼音首字母,如果非汉字字符（英文or数字），返回原字符不变

oracle汉字转拼音(获得全拼/拼音首字母/拼音截取等)效果如下: Oracle 字符集 GBK 没有问题 , UTF -8 需要修改一下Sql代码
--oracle汉字转拼音 PACKAGE
--1.获得全拼
SELECT GETHZPY.GETHZFULLPY('汉字') FROM DUAL;结果 : HanZi
--2.拼音首字母
SELECT GETHZPY.GETHZPYCAP('汉字') FROM DUAL;结果 : HZ
--3.拼音截取等
SELECT GETHZPY.GETHZPYCAPSUBSTR('汉字', 0, 1) FROM DUAL;结果 : H
代码部分太长挂在附件上以下代码如果在 PL/SQL Developer 执行的话,选择 Command Window 粘贴.
附件在最下面.
oracle汉字转拼音package_获得全拼——拼音首字母_拼音截取等.zip (35.9 KB)

‘柒’ 怎么在SQL SERVER中把汉字转化为拼音码

‘捌’ 汉字转拼音的sql函数为什么用到这些字 :骜簿错鵽樲鳆腂夻攈穒鱳旀桛沤曝囕鶸蜶箨鹜鑂韵咗，求原理

没人回答，自己找到了答案，整个sql排序规则是按照拼音排序，以此句sql为例：'A' AS PY,N'骜' AS word ”骜“字代表的是以A开头的拼音，并且音调是也要排最后的一个字，读做“ào”，所以得出在这个区间的汉字首字母都为A，以此类推可查出其他字母开头的汉字首字母

sql函数汉字转拼音

与sql函数汉字转拼音相关的内容