SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 comma separated value statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/15/2013 :  05:25:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 10/15/2013 :  05:38:35  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/15/2013 :  07:55:59  Show Profile  Reply with Quote
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.

Edited by - learning_grsql on 10/15/2013 08:04:27
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 10/15/2013 :  08:35:38  Show Profile  Reply with Quote
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 + '',%'''
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/15/2013 :  09:54:38  Show Profile  Reply with Quote
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 ','
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 10/15/2013 :  10:41:51  Show Profile  Reply with Quote
Of course! Sorry about that:
declare @csv varchar(max)
set @csv = '535,232'

print 'Select *
from tbl
where '',''+''' + @csv + '''+'','' like ''%,'' + mainid + '',%'''
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/15/2013 :  12:58:23  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 10/15/2013 :  14:53:36  Show Profile  Reply with Quote
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)) + ',%'
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/15/2013 :  15:28:06  Show Profile  Reply with Quote
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.

Edited by - learning_grsql on 10/15/2013 15:30:56
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 10/15/2013 :  15:58:19  Show Profile  Reply with Quote
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);
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 10/16/2013 :  11:57:40  Show Profile  Reply with Quote
Thanks James K :) . I got it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000