sql関数--漢字のピンイン変換(親測定sqlserverが使用可能)

1981 ワード

簡略スペルコードの取得:メソッドselect dboを呼び出す.procGetPY('テスト');
Create FUNCTION dbo.procGetPY 
( 
@str NVARCHAR(4000) 
) 
/*
select dbo. procGetPYFirstLetter (' ')
*/
RETURNS NVARCHAR(4000) 
--WITH ENCRYPTION 
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

スペルコードの取得:
create function [dbo].procGetPinYin(@str varchar(100))
returns varchar(8000)
as
begin
 declare @re varchar(8000),@crs varchar(10)
 declare @strlen int 
 select @strlen=len(@str),@re=''
 while @strlen>0
 begin  
  set @crs= substring(@str,@strlen,1)
      select @re=
        case
        when @crs