DEMO1:根据字符串和拆分字符,将字符串转化成表
函数:
ALTER function [dbo].[fn_split](@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (Result varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator , @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr , @i - 1))
set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
set @i = charindex(@seprator , @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
调用:
select Result as CityName from fn_split('浙江,江苏,上海', ',')
结果图:
DEMO2
USE [Test]
GO
/****** Object: UserDefinedFunction [dbo].[fun_test2] Script Date: 2016/3/9 13:59:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fun_test2](@StudentId1 INT,@StudentId2 INT)
RETURNS @Result TABLE
(
StudentId INT,
StudentName NVARCHAR(50)
)
AS
BEGIN
INSERT INTO @Result SELECT StudentId,StudentName FROM [dbo].[Student] WHERE StudentId=@StudentId1
INSERT INTO @Result SELECT StudentId,StudentName FROM [dbo].[Student] WHERE StudentId=@StudentId2
RETURN
END
--调用--
--SELECT * FROM [dbo].[fun_test2](1,3)--