1.取汉字的首字母

代码如下：

View Code 

SET  ANSI_NULLS ON
SET  QUOTED_IDENTIFIER ON
go

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

SELECT dbo.fun_getPY('来1吧')

运行结果如下：

L1B

2.取汉字胡全拼

代码如下：

View Code 
CREATE 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'bai',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'dao',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'du',N'蠹'
    UNION ALL SELECT'duan',N'叾'--籪叾
    UNION ALL SELECT'dui',N'譵'
    UNION ALL SELECT'dun',N'踲'
    UNION ALL SELECT'duo',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'饚'
    UNION ALL SELECT'han',N'鶾'
    UNION ALL SELECT'hang',N'沆'
    UNION ALL SELECT'hao',N'兞'
    UNION ALL SELECT'he',N'靏'
    UNION ALL SELECT'hei',N'嬒'
    UNION ALL SELECT'hen',N'恨'
    UNION ALL SELECT'heng',N'堼'--堼囍
    UNION ALL SELECT'hong',N'鬨'
    UNION ALL SELECT'hou',N'鱟'
    UNION ALL SELECT'hu',N'鸌'
    UNION ALL SELECT'hua',N'蘳'
    UNION ALL SELECT'huai',N'蘾'
    UNION ALL SELECT'huan',N'鰀'
    UNION ALL SELECT'huang',N'鎤'
    UNION ALL SELECT'hui',N'顪'
    UNION ALL SELECT'hun',N'諢'
    UNION ALL SELECT'huo',N'夻'
    UNION ALL SELECT'ji',N'驥'
    UNION ALL SELECT'jia',N'嗧'
    UNION ALL SELECT'jian',N'鑳'
    UNION ALL SELECT'jiang',N'謽'
    UNION ALL SELECT'jiao',N'釂'
    UNION ALL SELECT'jie',N'繲'
    UNION ALL SELECT'jin',N'齽'
    UNION ALL SELECT'jing',N'竸'
    UNION ALL SELECT'jiong',N'蘔'
    UNION ALL SELECT'jiu',N'欍'
    UNION ALL SELECT'ju',N'爠'
    UNION ALL SELECT'juan',N'羂'
    UNION ALL SELECT'jue',N'钁'
    UNION ALL SELECT'jun',N'攈'
    UNION ALL SELECT'ka',N'鉲'
    UNION ALL SELECT'kai',N'乫'--鎎乫
    UNION ALL SELECT'kan',N'矙'
    UNION ALL SELECT'kang',N'閌'
    UNION ALL SELECT'kao',N'鯌'
    UNION ALL SELECT'ke',N'騍'
    UNION ALL SELECT'ken',N'褃'
    UNION ALL SELECT'keng',N'鏗'--巪乬唟厼怾
    UNION ALL SELECT'kong',N'廤'
    UNION ALL SELECT'kou',N'鷇'
    UNION ALL SELECT'ku',N'嚳'
    UNION ALL SELECT'kua',N'骻'
    UNION ALL SELECT'kuai',N'鱠'
    UNION ALL SELECT'kuan',N'窾'
    UNION ALL SELECT'kuang',N'鑛'
    UNION ALL SELECT'kui',N'鑎'
    UNION ALL SELECT'kun',N'睏'
    UNION ALL SELECT'kuo',N'穒'
    UNION ALL SELECT'la',N'鞡'
    UNION ALL SELECT'lai',N'籟'
    UNION ALL SELECT'lan',N'糷'
    UNION ALL SELECT'lang',N'唥'
    UNION ALL SELECT'lao',N'軂'
    UNION ALL SELECT'le',N'餎'
    UNION ALL SELECT'lei',N'脷'--嘞脷
    UNION ALL SELECT'leng',N'睖'
    UNION ALL SELECT'li',N'瓈'
    UNION ALL SELECT'lia',N'倆'
    UNION ALL SELECT'lian',N'纞'
    UNION ALL SELECT'liang',N'鍄'
    UNION ALL SELECT'liao',N'瞭'
    UNION ALL SELECT'lie',N'鱲'
    UNION ALL SELECT'lin',N'轥'--轥拎
    UNION ALL SELECT'ling',N'炩'
    UNION ALL SELECT'liu',N'咯'--瓼甅囖咯
    UNION ALL SELECT'long',N'贚'
    UNION ALL SELECT'lou',N'鏤'
    UNION ALL SELECT'lu',N'氇'
    UNION ALL SELECT'lv',N'鑢'
    UNION ALL SELECT'luan',N'亂'
    UNION ALL SELECT'lue',N'擽'
    UNION ALL SELECT'lun',N'論'
    UNION ALL SELECT'luo',N'鱳'
    UNION ALL SELECT'ma',N'嘛'
    UNION ALL SELECT'mai',N'霢'
    UNION ALL SELECT'man',N'蘰'
    UNION ALL SELECT'mang',N'蠎'
    UNION ALL SELECT'mao',N'唜'
    UNION ALL SELECT'me',N'癦'--癦呅
    UNION ALL SELECT'mei',N'嚜'
    UNION ALL SELECT'men',N'們'
    UNION ALL SELECT'meng',N'霥'--霿踎
    UNION ALL SELECT'mi',N'羃'
    UNION ALL SELECT'mian',N'麵'
    UNION ALL SELECT'miao',N'廟'
    UNION ALL SELECT'mie',N'鱴'--鱴瓱
    UNION ALL SELECT'min',N'鰵'
    UNION ALL SELECT'ming',N'詺'
    UNION ALL SELECT'miu',N'謬'
    UNION ALL SELECT'mo',N'耱'--耱乮
    UNION ALL SELECT'mou',N'麰'--麰蟱
    UNION ALL SELECT'mu',N'旀'
    UNION ALL SELECT'na',N'魶'
    UNION ALL SELECT'nai',N'錼'
    UNION ALL SELECT'nan',N'婻'
    UNION ALL SELECT'nang',N'齉'
    UNION ALL SELECT'nao',N'臑'
    UNION ALL SELECT'ne',N'呢'
    UNION ALL SELECT'nei',N'焾'--嫩焾
    UNION ALL SELECT'nen',N'嫩'
    UNION ALL SELECT'neng',N'能'--莻嗯鈪銰啱
    UNION ALL SELECT'ni',N'嬺'
    UNION ALL SELECT'nian',N'艌'
    UNION ALL SELECT'niang',N'釀'
    UNION ALL SELECT'niao',N'脲'
    UNION ALL SELECT'nie',N'钀'
    UNION ALL SELECT'nin',N'拰'
    UNION ALL SELECT'ning',N'濘'
    UNION ALL SELECT'niu',N'靵'
    UNION ALL SELECT'nong',N'齈'
    UNION ALL SELECT'nou',N'譳'
    UNION ALL SELECT'nu',N'搙'
    UNION ALL SELECT'nv',N'衄'
    UNION ALL SELECT'nue',N'瘧'
    UNION ALL SELECT'nuan',N'燶'--硸黁燶郍
    UNION ALL SELECT'nuo',N'桛'
    UNION ALL SELECT'o',N'鞰'--毮夞乯鞰
    UNION ALL SELECT'ou',N'漚'
    UNION ALL SELECT'pa',N'袙'
    UNION ALL SELECT'pai',N'磗'--鎃磗
    UNION ALL SELECT'pan',N'鑻'
    UNION ALL SELECT'pang',N'胖'
    UNION ALL SELECT'pao',N'礮'
    UNION ALL SELECT'pei',N'轡'
    UNION ALL SELECT'pen',N'喯'
    UNION ALL SELECT'peng',N'喸'--浌巼闏乶喸
    UNION ALL SELECT'pi',N'鸊'
    UNION ALL SELECT'pian',N'騙'
    UNION ALL SELECT'piao',N'慓'
    UNION ALL SELECT'pie',N'嫳'
    UNION ALL SELECT'pin',N'聘'
    UNION ALL SELECT'ping',N'蘋'
    UNION ALL SELECT'po',N'魄'
    UNION ALL SELECT'pou',N'哛'--兺哛
    UNION ALL SELECT'pu',N'曝'
    UNION ALL SELECT'qi',N'蟿'
    UNION ALL SELECT'qia',N'髂'
    UNION ALL SELECT'qian',N'縴'
    UNION ALL SELECT'qiang',N'瓩'--羻兛瓩
    UNION ALL SELECT'qiao',N'躈'
    UNION ALL SELECT'qie',N'籡'
    UNION ALL SELECT'qin',N'藽'
    UNION ALL SELECT'qing',N'櫦'
    UNION ALL SELECT'qiong',N'瓗'
    UNION ALL SELECT'qiu',N'糗'
    UNION ALL SELECT'qu',N'覻'
    UNION ALL SELECT'quan',N'勸'
    UNION ALL SELECT'que',N'礭'
    UNION ALL SELECT'qun',N'囕'
    UNION ALL SELECT'ran',N'橪'
    UNION ALL SELECT'rang',N'讓'
    UNION ALL SELECT'rao',N'繞'
    UNION ALL SELECT're',N'熱'
    UNION ALL SELECT'ren',N'餁'
    UNION ALL SELECT'reng',N'陾'
    UNION ALL SELECT'ri',N'馹'
    UNION ALL SELECT'rong',N'穃'
    UNION ALL SELECT'rou',N'嶿'
    UNION ALL SELECT'ru',N'擩'
    UNION ALL SELECT'ruan',N'礝'
    UNION ALL SELECT'rui',N'壡'
    UNION ALL SELECT'run',N'橍'--橍挼
    UNION ALL SELECT'ruo',N'鶸'
    UNION ALL SELECT'sa',N'栍'--櫒栍
    UNION ALL SELECT'sai',N'虄'--簺虄
    UNION ALL SELECT'san',N'閐'
    UNION ALL SELECT'sang',N'喪'
    UNION ALL SELECT'sao',N'髞'
    UNION ALL SELECT'se',N'飋'--裇聓
    UNION ALL SELECT'sen',N'篸'
    UNION ALL SELECT'seng',N'縇'--閪縇
    UNION ALL SELECT'sha',N'霎'
    UNION ALL SELECT'shai',N'曬'
    UNION ALL SELECT'shan',N'鱔'
    UNION ALL SELECT'shang',N'緔'
    UNION ALL SELECT'shao',N'潲'
    UNION ALL SELECT'she',N'欇'
    UNION ALL SELECT'shen',N'瘮'
    UNION ALL SELECT'sheng',N'賸'
    UNION ALL SELECT'shi',N'瓧'--鰘齛兙瓧
    UNION ALL SELECT'shou',N'鏉'
    UNION ALL SELECT'shu',N'虪'
    UNION ALL SELECT'shua',N'誜'
    UNION ALL SELECT'shuai',N'卛'
    UNION ALL SELECT'shuan',N'腨'
    UNION ALL SELECT'shuang',N'灀'
    UNION ALL SELECT'shui',N'睡'
    UNION ALL SELECT'shun',N'鬊'
    UNION ALL SELECT'shuo',N'鑠'
    UNION ALL SELECT'si',N'乺'--瀃螦乺
    UNION ALL SELECT'song',N'鎹'
    UNION ALL SELECT'sou',N'瘶'
    UNION ALL SELECT'su',N'鷫'
    UNION ALL SELECT'suan',N'算'
    UNION ALL SELECT'sui',N'鐩'
    UNION ALL SELECT'sun',N'潠'
    UNION ALL SELECT'suo',N'蜶'
    UNION ALL SELECT'ta',N'襨'--躢襨
    UNION ALL SELECT'tai',N'燤'
    UNION ALL SELECT'tan',N'賧'
    UNION ALL SELECT'tang',N'燙'
    UNION ALL SELECT'tao',N'畓'--討畓
    UNION ALL SELECT'te',N'蟘'
    UNION ALL SELECT'teng',N'朰'--霯唞朰
    UNION ALL SELECT'ti',N'趯'
    UNION ALL SELECT'tian',N'舚'
    UNION ALL SELECT'tiao',N'糶'
    UNION ALL SELECT'tie',N'餮'
    UNION ALL SELECT'ting',N'乭'--濎乭
    UNION ALL SELECT'tong',N'憅'
    UNION ALL SELECT'tou',N'透'
    UNION ALL SELECT'tu',N'鵵'
    UNION ALL SELECT'tuan',N'褖'
    UNION ALL SELECT'tui',N'駾'
    UNION ALL SELECT'tun',N'坉'
    UNION ALL SELECT'tuo',N'籜'
    UNION ALL SELECT'wa',N'韤'
    UNION ALL SELECT'wai',N'顡'
    UNION ALL SELECT'wan',N'贎'
    UNION ALL SELECT'wang',N'朢'
    UNION ALL SELECT'wei',N'躛'
    UNION ALL SELECT'wen',N'璺'
    UNION ALL SELECT'weng',N'齆'
    UNION ALL SELECT'wo',N'齷'
    UNION ALL SELECT'wu',N'鶩'
    UNION ALL SELECT'xi',N'衋'
    UNION ALL SELECT'xia',N'鏬'
    UNION ALL SELECT'xian',N'鼸'
    UNION ALL SELECT'xiang',N'鱌'
    UNION ALL SELECT'xiao',N'斆'
    UNION ALL SELECT'xie',N'躞'
    UNION ALL SELECT'xin',N'釁'
    UNION ALL SELECT'xing',N'臖'
    UNION ALL SELECT'xiong',N'敻'
    UNION ALL SELECT'xiu',N'齅'
    UNION ALL SELECT'xu',N'蓿'
    UNION ALL SELECT'xuan',N'贙'
    UNION ALL SELECT'xue',N'瀥'
    UNION ALL SELECT'xun',N'鑂'
    UNION ALL SELECT'ya',N'齾'
    UNION ALL SELECT'yan',N'灩'
    UNION ALL SELECT'yang',N'樣'
    UNION ALL SELECT'yao',N'鑰'
    UNION ALL SELECT'ye',N'岃'--鸈膶岃
    UNION ALL SELECT'yi',N'齸'
    UNION ALL SELECT'yin',N'檼'
    UNION ALL SELECT'ying',N'譍'
    UNION ALL SELECT'yo',N'喲'
    UNION ALL SELECT'yong',N'醟'
    UNION ALL SELECT'you',N'鼬'
    UNION ALL SELECT'yu',N'爩'
    UNION ALL SELECT'yuan',N'願'
    UNION ALL SELECT'yue',N'鸙'
    UNION ALL SELECT'yun',N'韻'
    UNION ALL SELECT'za',N'雥'
    UNION ALL SELECT'zai',N'縡'
    UNION ALL SELECT'zan',N'饡'
    UNION ALL SELECT'zang',N'臟'
    UNION ALL SELECT'zao',N'竈'
    UNION ALL SELECT'ze',N'稄'
    UNION ALL SELECT'zei',N'鱡'
    UNION ALL SELECT'zen',N'囎'
    UNION ALL SELECT'zeng',N'贈'
    UNION ALL SELECT'zha',N'醡'
    UNION ALL SELECT'zhai',N'瘵'
    UNION ALL SELECT'zhan',N'驏'
    UNION ALL SELECT'zhang',N'瞕'
    UNION ALL SELECT'zhao',N'羄'
    UNION ALL SELECT'zhe',N'鷓'
    UNION ALL SELECT'zhen',N'黮'
    UNION ALL SELECT'zheng',N'證'
    UNION ALL SELECT'zhi',N'豒'
    UNION ALL SELECT'zhong',N'諥'
    UNION ALL SELECT'zhou',N'驟'
    UNION ALL SELECT'zhu',N'鑄'
    UNION ALL SELECT'zhua',N'爪'
    UNION ALL SELECT'zhuai',N'跩'
    UNION ALL SELECT'zhuan',N'籑'
    UNION ALL SELECT'zhuang',N'戅'
    UNION ALL SELECT'zhui',N'鑆'
    UNION ALL SELECT'zhun',N'稕'
    UNION ALL SELECT'zhuo',N'籱'
    UNION ALL SELECT'zi',N'漬'--漬唨
    UNION ALL SELECT'zong',N'縱'
    UNION ALL SELECT'zou',N'媰'
    UNION ALL SELECT'zu',N'謯'
    UNION ALL SELECT'zuan',N'攥'
    UNION ALL SELECT'zui',N'欈'
    UNION ALL SELECT'zun',N'銌'
    UNION ALL SELECT'zuo',N'咗') t  
    WHERE  word >= @word COLLATE  Chinese_PRC_CS_AS_KS_WS  
    ORDER BY  word COLLATE  Chinese_PRC_CS_AS_KS_WS ASC)ELSE  @word END ) 
    SET @i = @i + 1 
    END  
    RETURN  @pinyin   

END   

GO
SELECT dbo.fn_GetPinyin('阿门')
返回结果：

a men