SQL Server 自定义函数总结:
1.获取拼音首字母的函数
--获取拼音首字母的函数
--生成拼音首码
CREATE FUNCTION fn_GetPy(@str Nvarchar(4000))
RETURNS Nvarchar(4000)
--WITH ENCRYPTION --SQL Server加密
AS
BEGIN
DECLARE @intLen INT
DECLARE @strRet Nvarchar(4000)
DECLARE @temp Nvarchar(100)
SET @intLen = LEN(@str)
SET @strRet = ''
WHILE @intLen > 0
BEGIN
SET @temp = ''
SELECT @temp = (CASE WHEN SUBSTRING(@str,@intLen,1) >= '帀' THEN 'Z'
WHEN SUBSTRING(@str,@intLen,1) >= '丫' THEN 'Y'
WHEN SUBSTRING(@str,@intLen,1) >= '夕' THEN 'X'
WHEN SUBSTRING(@str,@intLen,1) >= '屲' THEN 'W'
WHEN SUBSTRING(@str,@intLen,1) >= '他' THEN 'T'
WHEN SUBSTRING(@str,@intLen,1) >= '仨' THEN 'S'
WHEN SUBSTRING(@str,@intLen,1) >= '呥' THEN 'R'
WHEN SUBSTRING(@str,@intLen,1) >= '七' THEN 'Q'
WHEN SUBSTRING(@str,@intLen,1) >= '妑' THEN 'P'
WHEN SUBSTRING(@str,@intLen,1) >= '噢' THEN 'O'
WHEN SUBSTRING(@str,@intLen,1) >= '拏' THEN 'N'
WHEN SUBSTRING(@str,@intLen,1) >= '嘸' THEN 'M'
WHEN SUBSTRING(@str,@intLen,1) >= '垃' THEN 'L'
WHEN SUBSTRING(@str,@intLen,1) >= '咔' THEN 'K'
WHEN SUBSTRING(@str,@intLen,1) >= '丌' THEN 'J'
WHEN SUBSTRING(@str,@intLen,1) >= '铪' THEN 'H'
WHEN SUBSTRING(@str,@intLen,1) >= '旮' THEN 'G'
WHEN SUBSTRING(@str,@intLen,1) >= '发' THEN 'F'
WHEN SUBSTRING(@str,@intLen,1) >= '妸' THEN 'E'
WHEN SUBSTRING(@str,@intLen,1) >= '咑' THEN 'D'
WHEN SUBSTRING(@str,@intLen,1) >= '嚓' THEN 'C'
WHEN SUBSTRING(@str,@intLen,1) >= '八' THEN 'B'
WHEN SUBSTRING(@str,@intLen,1) >= '吖' THEN 'A'
ELSE RTRIM(LTRIM(SUBSTRING(@str,@intLen,1))) END)
--对于汉字特殊字符,不生成拼音码
IF (ASCII(@temp)>127) SET @temp = ''
--对于英文中小括号,不生成拼音码
IF @temp = '(' or @temp = ')' set @temp = ''
SELECT @strRet = @temp + @strRet
SET @intLen = @intLen - 1
END
RETURN UPPER(@strRet)
END
----测试
--SELECT [Spell]=dbo.fn_GetPy('胡啊人')
二、sql server 自定义split 标值函数
--sql server 自定义split 标值函数
--自定义一个函数,分隔一个以分隔符的隔开字符串,例如把‘1,3,5,7,9’ 变成 数字1 3 5 7 9的结果集。
create function [dbo].[my_split](@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql)) --除去前后空格字符
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
--测试:
--declare @sql nvarchar(2000)
--set @sql='1,3,5,7,9'
--select * from Northwind.dbo.my_split(@sql,',')