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