Sql Server中函数使用，处理特定数据的分割多行、多值拆分代入查询等
一、表值函数
1.内联表值函数
表值函数返回 table 数据类型。对于内联表值函数，没有函数主体；表是单个 SELECT 语句的结果集
函数：
CREATE FUNCTION Fun_GetStudents ( @age INT )--参数
RETURNS TABLE --返回类型为表
AS
RETURN
( SELECT *
FROM dbo.Student
WHERE Sage = @age --通过一条sql查询语句获取表中数据
);
执行：
SELECT * FROM Fun_GetStudents(18);
函数：指定位置元素获取
CREATE FUNCTION dbo.GetSplitElement(
@s NVARCHAR(MAX),
@split NVARCHAR(10),
@index INT
) RETURNS NVARCHAR(100) AS BEGIN
DECLARE @result NVARCHAR(100);
DECLARE @pos INT = 1;
WHILE @index > 0 AND CHARINDEX(@split, @s) > 0 BEGIN
SET @result = LEFT(@s, CHARINDEX(@split, @s) - 1);
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s), '');
SET @index = @index - 1;
END
RETURN CASE WHEN @index = 0 THEN @result ELSE @s END;
END;
执行：
函数：元素计数函数
CREATE FUNCTION dbo.CountSplitElements(
@s NVARCHAR(MAX),
@split NVARCHAR(10)
) RETURNS INT AS BEGIN
DECLARE @count INT = 0;
WHILE CHARINDEX(@split, @s) > 0 BEGIN
SET @count = @count + 1;
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s), '');
END
RETURN @count + 1;
END;
执行：
函数：循环截取法
CREATE FUNCTION dbo.SplitString(
@s NVARCHAR(MAX),
@split NVARCHAR(10)
) RETURNS @re TABLE (col NVARCHAR(100))
AS BEGIN
WHILE CHARINDEX(@split, @s) > 0 BEGIN
INSERT @re VALUES(LEFT(@s, CHARINDEX(@split, @s) - 1));
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s), '');
END
INSERT @re VALUES(@s);
RETURN;
END;
执行：
函数运行：
a.按 、 拆分列数据内容成多行
select rst.value,* from
FDA_DJML as ewcdd
CROSS APPLY STRING_SPLIT(ewcdd.CCRYDM,'、') rst
b.
和in搭配使用
有些应用中，参数的值是不确定的，是用逗号拼接后，传入sql中，这个时候string_split特别有用，可以和in搭配使用。
SELECT
t.id,
t.name,
t.description
FROM test t
WHERE t.id IN (SELECT CAST(v.value AS INT) AS id FROM STRING_SPLIT('1,2,3', ',') v)
c.
2.多语句表值函数
多语句表值函数，在 BEGIN...END 语句块中定义的函数体包含一系列 Transact-SQL 语句，这些语句可生成行并将其插入将返回的表中
函数：
GO
/****** Object: UserDefinedFunction [dbo].[StringSplit] Script Date: 2018/5/25 9:38:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[StringSplit]
(
@Text NVARCHAR(MAX),
@Sign NVARCHAR(MAX)
)
RETURNS
@ResultTable TABLE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
TextValue NVARCHAR(1024)
)
AS
BEGIN
DECLARE @StartIndex INT
DECLARE @FindIndex INT
DECLARE @Content VARCHAR(4000)
-- 和函数CHARINDEX有关
SET @StartIndex=1
SET @FindIndex=0
WHILE(@StartIndex<=LEN(@Text))
BEGIN
SET @FindIndex=CHARINDEX(@Sign, @Text, @StartIndex)
IF(@FindIndex=0 OR @FindIndex IS NULL)
BEGIN
-- 查找完毕
SET @FindIndex=LEN(@Text)+1
END
SET @Content=LTRIM(RTRIM(SUBSTRING(@Text, @StartIndex, @FindIndex-@StartIndex)))
-- 下次查找的位置
SET @StartIndex=@FindIndex+1
-- 插入结果
IF(LEN(@Content)>0)
BEGIN
INSERT INTO @ResultTable(TextValue) VALUES (@Content)
END
END
RETURN
END
GO
执行：
SELECT * FROM StringSplit('a3,b,5c,d,e,f,g', ',')
在SQL 兼容级别 130 里面对应有内置该函数--
SELECT row_number() over(order by VALUE) as rownum,value as A FROM StringSplit('a3,b,5c,d,e,f,g', ',')
SELECT FIELD10,* FROM OER_DJJSFS T CROSS APPLY STRING_SPLIT(T.FIELD10,'、');
--查询数据库兼容性级别
SELECT name,compatibility_level
FROM sys.databases
WHERE name = 'TEST';
--修改兼容级别
-- 兼容级别 100 示例配置
ALTER DATABASE TestDB SET COMPATIBILITY_LEVEL = 100;
-- 兼容级别 130 示例配置
ALTER DATABASE TEST SET COMPATIBILITY_LEVEL = 130;
二、标量值函数
标量函数（返回一个具体类型的值而不是一张表了）
这个函数很简单返回一个整型值，然后就可以在存储过程中调用了，不过调用的方式有所不同，象上面的表值函数调用是不需要所有者的，只要写函数名称就可以，对于标量值函数来说，是需要加上所有者的，比如所有者是dbo
CREATE FUNCTION FUN_DataFormat (@strDate datetime)
RETURNS varchar(20) AS
BEGIN
declare @date varchar(20)
set @date = DATENAME(YY,@strDate)+'年'+Convert(VARCHAR,MONTH(@strDate))+'月'+Convert(VARCHAR,DAY(@strDate))+'日'
return @date
END
select dbo.FUN_DataFormat(getdate()) --（用的时候一定要在函数前面添加一个dbo或者的话数据库语句报错的会提示不认识的）
在T-SQL中我们经常批量操作时都会对字符串进行拆分，可是SQL Server中却没有自带Split函数，所以要自己来实现了。这里将字符串分割以table形式输出
语法如下：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
create by shuke.li 2020-9-15
*/
create function [dbo].[SplitString]
(
@Input nvarchar(max), --input string to be separated
@Separator nvarchar(max)=',', --a string that delimit the substrings in the input string
@RemoveEmptyEntries bit=1 --the return value does not include array elements that contain an empty string
)
returns @TABLE table
(
[Id] int identity(1,1),
[Value] nvarchar(max)
)
as
begin
declare @Index int, @Entry nvarchar(max)
set @Index = charindex(@Separator,@Input)
while (@Index>0)
begin
set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))
if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
begin
insert into @TABLE([Value]) Values(@Entry)
end
set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
set @Index = charindex(@Separator, @Input)
end
set @Entry=ltrim(rtrim(@Input))
if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
begin
insert into @TABLE([Value]) Values(@Entry)
end
return
end
只要在新建查询里执行上面的代码，即完成了split函数的建立。
下面来测试这一函数的功能，测试所使用的的SQL脚本如下：
declare @str1 varchar(max) set @str1 = 'CFER-3345-3323,CFER-0023-2299,CFER-0023-6677,CFER-0023-7678,CFER-4565-2299,CFER-0023-6678' select * from [dbo].[SplitString](@str1, ',', 0)
浙公网安备 33010602011771号