一、创建测试表
Employees
二、创建表值函数
-- DROP FUNCTION TableIntSplit;
CREATE FUNCTION TableIntSplit(@Text NVARCHAR(4000),@Sign NVARCHAR(4000))
RETURNS @tempTable TABLE(Id INT )
AS
BEGIN
DECLARE @StartIndex INT
DECLARE @FindIndex INT
DECLARE @Content VARCHAR(4000)
SET @StartIndex = 1
SET @FindIndex=0
WHILE(@StartIndex <= LEN(@Text))
BEGIN
SELECT @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
INSERT INTO @tempTable ([Id]) VALUES (convert(int, @Content))
END
RETURN
END;
测试文章来源:https://www.toymoban.com/news/detail-617435.html
select * from Employees(NOLOCK)
select * from dbo.TableIntSplit('1,2,3,4,5,6',',')
select t1.LastName,t2.Id from Employees(NOLOCK) t1 join dbo.TableIntSplit('1,2,3,4,5,6',',') t2 on t1.EmployeeID=t2.Id
文章来源地址https://www.toymoban.com/news/detail-617435.html
到了这里,关于sql server表值函数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!