CREATE FUNCTION dbo.fn_GetPinyinInitial (@str NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @word NCHAR(1), @py NVARCHAR(4000) = N'';
WHILE LEN(@str) > 0
BEGIN
SET @word = LEFT(@str, 1);
-- 判斷是否為 CJK 統一漢字擴展區 (19968~40869)
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
UNICODE(@word) BETWEEN 19968 AND 40869 覆蓋基本區大多數常用漢字(\u4E00-\u9FFF)。Chinese_PRC_CS_AS_KS_WS 排序規則)。WHERE word >= @word ... ORDER BY PY ASC 取首個滿足條件的聲母,即該字所在聲母區。SELECT dbo.fn_GetPinyinInitial(N'張三李四') -- 返回 'ZSLS'
SELECT dbo.fn_GetPinyinInitial(N'你好2024') -- 返回 'NH2024'
Chinese_PRC_CS_AS_KS_WS(中文簡體_區分大小寫_區分重音_區分假名_區分寬度),缺失會報錯。[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString GetPinyinInitial(SqlString input)
{
if (input.IsNull) return SqlString.Null;
var sb = new System.Text.StringBuilder();
foreach (char c in input.Value)
{
if (c >= 0x4E00 && c <= 0x9FFF)
{
// 使用 Pinyin4net 或自帶映射表
sb.Append(GetInitial(c)); // 實現略
}
else sb.Append(c);
}
return new SqlString(sb.ToString());
}
部署後 CREATE ASSEMBLY + CREATE FUNCTION ... EXTERNAL NAME,性能提升 50-100 倍。