comma separated in where clause

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 in where clause

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-06-06 : 02:31:18

HI,

can you tell me what the difference between the following two codes in where clause?

The both provides the same resultset for me.


alter procedure
@fieldname varchar(50)
as
begin
select field1, field2 from table1
where ',' + @fieldname + ',' like '%,' + field3 + ',%'
end

The second code :


alter procedure
@fieldname varchar(50)
as
begin
select field1, field2 from table1
where  @fieldname like '%' + field3 + '%'
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-06-06 : 08:35:13

First one is more safer way of writing it
Second case since , is not included there's a chance it matches cases where pattern is present in the field rather than whole string

As an example consider you pass values as 'Test,Hello,Hi'
In first case you'll get records with the full words Test or Hello or Hi in column
In the second case because you dont put , delimiter it will look for pattern and will even match cases where column has Testing,Hit etc as it has patterns Test,Hi etc in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-06-06 : 10:04:44

Thanks Visakh. I got it now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources