中将中文转为拼音首字母的函数及调用方法

SQL Server 中将中文转为拼音首字母的函数及调用方法

1、创建函数
Create FUNCTION dbo.fnpbGetPYFirstLetter  
(  
@str NVARCHAR(4000)  
)  
/* 
select dbo. fnpbGetPYFirstLetter ('中國香港') 
*/ 
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

2、函数的调用
    select dbo.fnpbGetPYFirstLetter(name), name from tablename 
name：数据表中要转换为拼音首字母的字段

 

SQL函數提取数字、提取英文、提取中文、过滤重复字符等

--提取数字
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
--------------------------------------------------------------------
--提取英文
IF OBJECT_ID('DBO.GET_STR') IS NOT NULL
DROP FUNCTION DBO.GET_STR
GO
CREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--提取中文
IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULL
DROP FUNCTION DBO.CHINA_STR
GO
CREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN @S
END
GO
PRINT DBO.CHINA_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--过滤重复字符
IF OBJECT_ID('DBO.DISTINCT_STR') IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR
GO
CREATE FUNCTION DBO.DISTINCT_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
IF LEFT(@S,1)<>@SPLIT
SET @S = @SPLIT+@S
IF RIGHT(@S,1)<>@SPLIT
SET @S = @S+@SPLIT
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1
BEGIN
SET @INDEX = CHARINDEX(@SPLIT,@S)
SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
IF @NEW IS NULL
SET @NEW = ISNULL(@NEW,'')+@TEMP
ELSE
SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
WHILE CHARINDEX(@TEMP,@S)>0
BEGIN
SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')
END
END
RETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END
GO
PRINT DBO.DISTINCT_STR('A,A,B,C,C,B,C,',',')
--A,B,C
GO
--------------------------------------------------------------------
--过滤重复字符2
IF OBJECT_ID('DBO.DISTINCT_STR2') IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR2
GO
CREATE FUNCTION DBO.DISTINCT_STR2(@S varchar(8000))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
WHILE LEN(@S)>0
BEGIN
SET @NEW=ISNULL(@NEW,'')+LEFT(@S,1)
SET @S=REPLACE(@S,LEFT(@S,1),'')
END
RETURN @NEW
END
GO
SELECT DBO.DISTINCT_STR2('AABCCD')
--ABCD
GO

--------------------------------------------------------------------
IF OBJECT_ID('DBO.SPLIT_STR') IS NOT NULL
DROP FUNCTION DBO.SPLIT_STR
GO
CREATE FUNCTION DBO.SPLIT_STR(
@S varchar(8000),      --包含多个数据项的字符串
@INDEX int,             --要获取的数据项的位置
@SPLIT varchar(10)     --数据分隔符
)
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN int
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0
SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))
END
GO
PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|')
--
GO