Ⅰ 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語句如何實現將資料庫表中某個欄位的漢字批量轉為拼音
sql可以實現 但是你要准備 建立一個龐大的字典庫所有漢字拼音全存入 然後寫函數一個個比對返回
我都是用程序寫的 sql實現比較蛋痛
Ⅲ SQL 獲取名字拼音
做兩個函數。一個取漢字拼音首字母，一個取完整拼音
下面代碼是晚上搜到的：
--取漢字首字母函數
create function f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖','A' union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '發','F' union all
select '旮','G' union all select '鉿','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '嘸','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','T' union all select '屲','W' union all
select '夕','X' union all select '丫','Y' union all
select '帀','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go
--使用
select id,name,dbo.f_GetPy(name) from mytable
取完整拼音的跟上述f_GetPy函數類似，只是臨時表要多寫很多行，拼音有多少種組合就寫多少行，大概六七百行吧。你自己找找相關的其他語言轉拼音全碼的代碼，實現方式是相通的。
Ⅳ 怎麼在SQL SERVER中把漢字 轉化為 拼音碼
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 server根據名字生成拼音，怎麼做
sql server自動生成拼音首字母的函數
http://blog.csdn.net/luckeryin/article/details/3377378
Ⅵ 漢字轉拼音的sql函數為什麼用到這些字 :驁簿錯鵽樲鰒腂夻攈穒鱳旀桛漚曝囕鶸蜶籜鶩鑂韻咗，求原理
沒人回答，自己找到了答案，整個sql排序規則是按照拼音排序， 以此句sql為例：'A' AS PY,N'驁' AS word 」驁「字代表的是以A開頭的拼音，並且音調是也要排最後的一個字，讀做「ào」，所以得出在這個區間的漢字首字母都為A，以此類推可查出其他字母開頭的漢字首字母
Ⅶ 資料庫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語句 把用戶名中文改成拼音字母
看看這個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'饚'