USE [CTMS_TEST]
GO
/****** Object: UserDefinedFunction [dbo].[fun_getPY] Script Date: 07/19/2012 14:58:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
/****** Object: UserDefinedFunction [dbo].[fun_getPY] Script Date: 07/19/2012 14:58:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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