convert comma separated values into rows withou using UDF

Hi,
I'm looking for a way to turn csv like 4,5,6,3 into rows without using
UDF, so it can be used in pure T-SQL only.
I want to use it in a code generated sql string, that will use these
values with an IN statement.
for example:
if column VAL contains 1,2,3
I would like to write something like:
SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
work.
I also don't want to use CHARINDEX since this query should be as
efficient as possible.
I hope this can be done at all.
Thanks,
Eran.I think this is what you want:
DECLARE @.X NVARCHAR(4000)
SELECT @.X = 'SELECT columnList FROM x WHERE v IN (' + VAL + ')'
FROM tableName
EXEC(@.X)
But had the database been designed correctly, you wouldn't have to hope.
You could have simply used a join and avoided using dynamic SQL at all.
(1) You should never use SELECT * in production code.
(2) A column in a row (cell) should never have more than one value. In
other words, you should never store comma-separated values in a cell.
99.999% of the time, the presence of either of these is a sure sign of the
laziness, short-sightedness, and incompetence of the database designer or
developer.
"EranS" <eransevi@.yahoo.com> wrote in message
news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>|||thanks to brian heres a sample
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
"Brian Selzer" wrote:

> I think this is what you want:
> DECLARE @.X NVARCHAR(4000)
> SELECT @.X = 'SELECT columnList FROM x WHERE v IN (' + VAL + ')'
> FROM tableName
> EXEC(@.X)
> But had the database been designed correctly, you wouldn't have to hope.
> You could have simply used a join and avoided using dynamic SQL at all.
> (1) You should never use SELECT * in production code.
> (2) A column in a row (cell) should never have more than one value. In
> other words, you should never store comma-separated values in a cell.
> 99.999% of the time, the presence of either of these is a sure sign of the
> laziness, short-sightedness, and incompetence of the database designer or
> developer.
>
> "EranS" <eransevi@.yahoo.com> wrote in message
> news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
>
>|||Thanks Brian,
I can't run what you suggested since the values list should be
dynamically created when running the query, and it should both be in
one query.
This solution is only temporary until I'll normalize my tables better,
so I'm looking for a dirty way to do it. I guess I'll resort to using a
temporary table with the values in rows and then use it with the IN
clause. This won't be as fast as hoped but it should be better then
using a function.|||Take a look at this:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
(by Dejan Sarka)
ML|||Eran, there are some issues with your request...

> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
A UDF is pure T-SQL.

> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> ...
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
Using dynamic execution, you will end up with a different plan generated for
each unique string. Using the CHARINDEX method in a UDF that splits the arra
y
elements into multiple rows (or not encapsulated with a UDF), your code will
still be able to use an index on the table's join column, and reuse executio
n
plans.
That's really the way to go in this case.
My 2c (tested),
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"EranS" wrote:

> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>|||Using a table function to split a string into rows can in most cases prove t
o
be much faster than using the IN operator, since the latter gets translated
into several OR clauses, which slow down the query processing.
On the other hand the table function returns a table of values that can be
joined to the other table(s) in your query, thus taking advantage of the muc
h
faster JOIN operation.
ML|||SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF OBJECT_ID ('dbo.fnBAS_utlSplitIDsIntoTable') IS NOT NULL --AND
OBJECTPROPERTY ( OBJECT_ID('dbo.fnBAS_utlSplitIDsIntoTable') ,
'IsinLineFunction' ) = 1
DROP FUNCTION dbo.fnBAS_utlSplitIDsIntoTable
GO
CREATE FUNCTION dbo.fnBAS_utlSplitIDsIntoTable (@.sIDList varchar(8000),
@.Delimeter char(1))
RETURNS TABLE AS
-- Version : $Id: fnBAS_utlSplitIDsIntoTable.sql,v 1.7 2005/05/09 20:11:33
vladimirm Exp $
/*
Usage:
declare @.IDlist varchar(500)
set @.idlist ='12,11,4,23455,13'
select * from dbo.fnBAS_utlSplitIDsIntoTable(@.IDList,',')
*/
RETURN
(
SELECT 1 - LEN(REPLACE(LEFT(@.sIDList, Number), @.Delimeter, SPACE(0))) +
Number AS [idx]
,SUBSTRING( @.sIDList, Number, CHARINDEX(@.Delimeter, @.sIDList +
@.Delimeter, Number) - Number) AS [intID]
FROM dbo.Numbers_8000
WHERE SUBSTRING(@.Delimeter + @.sIDList, Number, 1) = @.Delimeter
AND Number < LEN(@.sIDList) + 1
)
GO
"EranS" <eransevi@.yahoo.com> wrote in message
news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>