• 【转】SQL汉字转换为拼音的函数


    SQL中有多种的函数,下面为您介绍sql中汉字转换为拼音的函数,供您参考。数据库中先自定义一个函数,再把下面代码写进去,功能是得到汉字拼音首字母:

    如下:

    SQL中有多种的函数,下面为您介绍sql中汉字转换为拼音的函数,供您参考。数据库中先自定义一个函数,再把下面代码写进去,功能是得到汉字拼音首字母:
    
    如下:
    
    
    view source 
    
    print?
    01 create function fun_getPY(@str nvarchar(4000))  
    
    02 returns nvarchar(4000)  
    
    03 as 
    
    04 begin 
    
    05 declare @word nchar(1),@PY nvarchar(4000)  
    
    06 set @PY='' 
    
    07 while len(@str)>0  
    
    08 begin 
    
    09 set @word=left(@str,1)  
    
    10 --如果非汉字字符,返回原字符  
    
    11 set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901  
    
    12 then (select top 1 PY from (  
    
    13 select 'A' as PY,N'' as word  
    
    14 union all select 'B',N'簿' 
    
    15 union all select 'C',N'' 
    
    16 union all select 'D',N'' 
    
    17 union all select 'E',N'' 
    
    18 union all select 'F',N'' 
    
    19 union all select 'G',N'' 
    
    20 union all select 'H',N'' 
    
    21 union all select 'J',N'' 
    
    22 union all select 'K',N'' 
    
    23 union all select 'L',N'' 
    
    24 union all select 'M',N'' 
    
    25 union all select 'N',N'' 
    
    26 union all select 'O',N'' 
    
    27 union all select 'P',N'' 
    
    28 union all select 'Q',N'' 
    
    29 union all select 'R',N'' 
    
    30 union all select 'S',N'' 
    
    31 union all select 'T',N'' 
    
    32 union all select 'W',N'' 
    
    33 union all select 'X',N'' 
    
    34 union all select 'Y',N'' 
    
    35 union all select 'Z',N'' 
    
    36 ) T   
    
    37 where word>=@word collate Chinese_PRC_CS_AS_KS_WS   
    
    38 order by PY ASC) else @word end)  
    
    39 set @str=right(@str,len(@str)-1)  
    
    40 end 
    
    41 return @PY  
    
    42 end 
    
    
    --函数调用实例:
    select dbo.fun_getPY('中华人民共和国')
    结果都为:ZHRMGHG
    01 create function fun_getPY(@str nvarchar(4000))
    02 returns nvarchar(4000)
    03 as
    04 begin
    05 declare @word nchar(1),@PY nvarchar(4000)
    06 set @PY=''
    07 while len(@str)>0
    08 begin
    09 set @word=left(@str,1)
    10 --如果非汉字字符,返回原字符
    11 set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
    12 then (select top 1 PY from (
    13 select 'A' as PY,N'驁' as word
    14 union all select 'B',N'簿'
    15 union all select 'C',N'錯'
    16 union all select 'D',N'鵽'
    17 union all select 'E',N'樲'
    18 union all select 'F',N'鰒'
    19 union all select 'G',N'腂'
    20 union all select 'H',N'夻'
    21 union all select 'J',N'攈'
    22 union all select 'K',N'穒'
    23 union all select 'L',N'鱳'
    24 union all select 'M',N'旀'
    25 union all select 'N',N'桛'
    26 union all select 'O',N'漚'
    27 union all select 'P',N'曝'
    28 union all select 'Q',N'囕'
    29 union all select 'R',N'鶸'
    30 union all select 'S',N'蜶'
    31 union all select 'T',N'籜'
    32 union all select 'W',N'鶩'
    33 union all select 'X',N'鑂'
    34 union all select 'Y',N'韻'
    35 union all select 'Z',N'咗'
    36 ) T 
    37 where word>=@word collate Chinese_PRC_CS_AS_KS_WS 
    38 order by PY ASC) else @word end)
    39 set @str=right(@str,len(@str)-1)
    40 end
    41 return @PY
    42 end

    --函数调用实例: select dbo.fun_getPY('中华人民共和国') 结果都为:ZHRMGHG

    欢迎关注我的微博:@机器学习日记 https://weibo.com/6382778167/profile?rightmod=1&wvr=6&mod=personinfo
  • 相关阅读:
    [多项式算法](Part 1)FFT 快速傅里叶变换 学习笔记
    [多项式算法](Part 4)FWT 快速沃尔什变换 学习笔记
    [多项式算法](Part 2)NTT 快速数论变换 学习笔记
    [多项式算法](Part 5)分治FFT 学习笔记
    [HDU4316]Mission Impossible(计算几何/凸包/半平面交)
    NOI2019 游记 | 在NOI寻求AC是否搞错了什么?
    [Android] Activity的四种launchMode
    [Android] CardView的使用及兼容
    [Android] 记录相对位置布局
    [Android] ConstraintLayout
  • 原文地址:https://www.cnblogs.com/danscarlett/p/4184065.html
Copyright © 2020-2023  润新知