有时侯有在服务端查询的时侯可能会用需要多个参数,而且位数是动态不一定的。
这个时侯可能就把一个list传递到sql里做为条件。
--
-- Split a list and return a table if Ids
-- Used to take a comma separated list as a string and return a table
--
CREATE FUNCTION [dbo].[SplitIDList]
(
@CommaSeparatedIDList NVARCHAR(1000)
)
RETURNS @List TABLE
(
Id INT
)
BEGIN
DECLARE @sItem VARCHAR(10);
WHILE CHARINDEX(‘,‘,@CommaSeparatedIDList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@CommaSeparatedIDList,1,CHARINDEX(‘,‘,@CommaSeparatedIDList,0)-1))),
@CommaSeparatedIDList=RTRIM(LTRIM(SUBSTRING(@CommaSeparatedIDList,CHARINDEX(‘,‘,@CommaSeparatedIDList,0)+1,LEN(@CommaSeparatedIDList))));
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem;
END;
IF LEN(@CommaSeparatedIDList) > 0
INSERT INTO @List SELECT @CommaSeparatedIDList; -- Put the last item in
RETURN
END
GO
来个例子看看:
DECLARE @test NVARCHAR(500);
SET @test = ‘1,2,3,9,4‘;
SELECT *
FROM [SplitIDList](@test);
输出:
CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置
还有一个类似的函数:PATINDEX
这两个函数常常用来在一段字符中搜索字符或者字符串。如果被搜索的字符中包含有要搜索的字符,那么这两个函数返回一个非零的整数,这个整数是要搜索的字符在被搜索的字符中的开始位数。
PATINDEX函数支持使用通配符来进行搜索
CHARINDEX不支持通佩符
另外附上来自MSDN的一个更加通用的做法:
CREATE FUNCTION dbo.UFN_SEPARATES_COLUMNS
(@TEXT VARCHAR(8000),
@COLUMN TINYINT,
@SEPARATOR CHAR(1)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @POS_START INT= 1;
DECLARE @POS_END INT= CHARINDEX(@SEPARATOR, @TEXT, @POS_START);
WHILE(@COLUMN > 1
AND @POS_END > 0)
BEGIN
SET @POS_START = @POS_END + 1;
SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START);
SET @COLUMN = @COLUMN - 1;
END;
IF @COLUMN > 1
SET @POS_START = LEN(@TEXT) + 1;
IF @POS_END = 0
SET @POS_END = LEN(@TEXT) + 1;
RETURN SUBSTRING(@TEXT, @POS_START, @POS_END-@POS_START);
END;
GO
测试:
DECLARE @IMPORTROW VARCHAR(500), @CD_PERSON INT, @NM_MAIL VARCHAR(25), @DT_CREATED DATE;
SET @IMPORTROW = ‘154198|2014-01-08|durval@test.com|Comments|123456|2015-10-30|‘;
SELECT @CD_PERSON = dbo.UFN_SEPARATES_COLUMNS(@IMPORTROW, 1, ‘|‘),
@DT_CREATED = CAST(dbo.UFN_SEPARATES_COLUMNS(@IMPORTROW, 2, ‘|‘) AS DATE),
@NM_MAIL = dbo.UFN_SEPARATES_COLUMNS(@IMPORTROW, 3, ‘|‘);
--THROUGH THESE VARIABLE, YOU CAN CHANGE
--YOUR DATA AFTER BEING SEPARATED
SELECT @CD_PERSON AS CD_PERSON,
@NM_MAIL AS NM_MAIL,
FORMAT(@DT_CREATED, ‘MM/dd/yyyy‘) AS DT_CREATED;
GO
结果:
本文出自 “狼之魂” 博客,请务必保留此出处http://lybing.blog.51cto.com/3286625/1752372
SQL 把(n)varchar类型转换为多列或多个value的集合