How to convert comma separated NVARCHAR to table records in SQL Server 2005?
sql, sql-server-2005
Solution
Possible duplicate of separate comma separated values and store in table in sql server.
Please try a precise one from Comma-Delimited Value to Table:
CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000), @Delimiter nvarchar(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN
DECLARE @String VARCHAR(10)
WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))
INSERT INTO @OutputTable ( [String] )
VALUES ( @String )
END
RETURN
END
GO
Check the requirement in other way using XML:
DECLARE @param NVARCHAR(MAX)
SET @param = '1:0,2:1,3:1,4:0'
SELECT
Split.a.value('.', 'VARCHAR(100)') AS CVS
FROM
(
SELECT CAST ('<M>' + REPLACE(@param, ',', '</M><M>') + '</M>' AS XML) AS CVS
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
Problem
I have a list of ids separated by comma like: ``` 1,17,25,44,46,67,88 ``` I want to convert them to a table records ( into a temporary table ) like ``` #tempTable number_ -------- 1 17 25 44 46 67 88 ``` It is possible with a function, a table-valued one ? Why I want this ? I want to use for `INNER JOIN` clause (into stored procedure) with another table(s) like as: ``` SELECT a,b,c FROM T1 INNER JOIN functionNameWhichReturnsTable ON functionNameWhichReturnsTable.number_ = T1.a ``` I cannot use `IN` because I will use stored procedure which accepts a parameter of type NVARCHAR. That parameter will provide the list of ids. Thank you