comma separated value statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

comma separated value statement

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-10-15 : 05:25:43

Hi, I'm trying to see the following comma separated sql statement using 'print' but it is throwing error "incorrect syntax near ','".


declare @csv varchar(max)
set @csv = '535,232'
print ''Select *
from tbl
where ',' + @csv + ',' like '%,' + mainid + ',%'''

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-10-15 : 05:38:35

declare @csv varchar(max)
set @csv = '535,232'

print 'Select *
from tbl
where '',''' + @csv + ''','' like ''%,'' + mainid + '',%'''

Too old to Rock'n'Roll too young to die.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-10-15 : 07:55:59

Thanks Webfred
However, the above code produces the following output


Select *
from tbl
where ','535,232',' like '%,' + mainid + ',%'

When I run this code as a new query, it produces blank result but in fact there are many rows in the table with mainid values 535 and 232.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-15 : 08:35:38

Adding couple of PLUS sign's to webfred's query:

declare @csv varchar(max)
set @csv = '535,232'

print 'Select *
from tbl
where '',''+' + @csv + '+'','' like ''%,'' + mainid + '',%'''

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-10-15 : 09:54:38

Thanks Jamesk, now the output is :


Select *
from tbl
where ','+535,232+',' like '%,' + mainid + ',%'

when I run the query, it gives error 'An expression of non-boolean type specified in a context where a condition is expected, near ','

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-15 : 10:41:51

Of course! Sorry about that:

declare @csv varchar(max)
set @csv = '535,232'

print 'Select *
from tbl
where '',''+''' + @csv + '''+'','' like ''%,'' + mainid + '',%'''

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-10-15 : 12:58:23

Thanks James again.

Now it gives the following output.


Select *
from tbl
where ','+'535,232'+',' like '%,' + mainid + ',%'

When I run this query, it executes successfuly but displays 0 rows affected. But, in fact, the table has lot of rows with these values 535 and 232.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-15 : 14:53:36

Not sure why the query is not picking those up. See the example below which works correctly.

CREATE TABLE #tmp
(
	mainid VARCHAR(32)
);
INSERT INTO #tmp VALUES
(535),(232),(222),(2322);


Select *
from #tmp
where ','+'535,232'+',' like '%,' + mainid + ',%'

DROP TABLE #tmp;

It could be that there are hidden characters or trailing or leading spaces in the column. IF that is the case, try this:

Select *
from #tmp
where ','+'535,232'+',' like '%,' + LTRIM(RTRIM(mainid)) + ',%'

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-10-15 : 15:28:06

Many Thanks James K. Great!

In fact there was an additional space at the right of every word. After I changed it to Rtrim(mainid), it worked.

I just have a quick question, however when I use query like select * from tbl where id = '252', it works perfect irrespective of the additional space.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-15 : 15:58:19

This is one of those funny things about SQL - trailing spaces don't count towards equality or length (although the same is not true for leading spaces)

-- these are equal
DECLARE @x1 VARCHAR(32) = 'x ';
DECLARE @x2 VARCHAR(32) = 'x                ';
IF @x1 = @x2 PRINT 'Yes, these are equal';
PRINT LEN(@x1);
PRINT LEN(@x2);

--- these are not equal
DECLARE @x1 VARCHAR(32) = ' y';
DECLARE @x2 VARCHAR(32) = '                y';
IF @x1 = @x2 PRINT 'Yes, these are equal';
PRINT LEN(@x1);
PRINT LEN(@x2);

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-10-16 : 11:57:40

Thanks James K :) . I got it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources