October 21, 2005 at 12:20 pm
That's because you need a trailing comma for it to work.. but please check my solution and ask question if you don't get it.
October 21, 2005 at 12:56 pm
You may also want to look into the following link which discusses a similar situation:
http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx
You just need to remove the single-quotes in your comma-delimited values to make it work.
Hope this helps.
October 23, 2005 at 5:10 am
Hi,
Try this
CREATE FUNCTION dbo.ufn_Split
(
@StringIn nvarchar(2000),
@Delimiter nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
SearchValues varchar(500)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@Delimiter,@StringIn)>0)
Begin
Insert Into @RtnValue (SearchValues)
Select SearchValues = ltrim(rtrim(Substring(@StringIn,1,Charindex(@Delimiter,@StringIn)-1)))
Set @StringIn = Substring(@StringIn,Charindex(@Delimiter,@StringIn)+1,len(@StringIn))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (SearchValues)
Select SearchValues = ltrim(rtrim(@StringIn))
Return
END
Your In() then becomes In(Select SearchValues from dbo.ufn_Split('John Doe,A Smith',','))
October 24, 2005 at 12:29 am
Solutions using dynamic sql, cursors or loops are really not something that should be considered. Remi (RGR'us) have already supplied a perfect answer (although I prefer my own variant, that works similarly, below) in the third post of this thread. If that is not enough, or you do not understand the answer, then the two articles he linked to should be read right away.
My own version of converting a delimited list to a set that can be joined to:
CREATE FUNCTION dbo.fnListToSet (@list VARCHAR(8000), @delimiter VARCHAR(3))
RETURNS TABLE
AS
RETURN
SELECT s.tuple
FROM (
SELECT SUBSTRING(
@delimiter + @list + @delimiter
, numbers.n + LEN(@delimiter)
, CHARINDEX(@delimiter, @delimiter + @list + @delimiter, numbers.n + LEN(@delimiter)) - numbers.n - LEN(@delimiter)
) AS tuple
FROM (
SELECT D0.d*1 + D1.d*10 + D2.d*100 + D3.d*1000
FROM (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D0 (d)
, (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D1 (d)
, (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D2 (d)
, (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D3 (d)
) AS numbers (n)
WHERE SUBSTRING(
@delimiter + @list + @delimiter
, numbers.n
, LEN(@delimiter)
) = @delimiter
AND n 0
GO
I hope the indenting comes out alright.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply