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.
| Author |
Topic |
|
bhushan.andhare
Starting Member
3 Posts |
 Posted - 2015-04-13 : 06:47:24
|
I have a simple query where I am using IN clause to search for numbers on column col1 .
The col1 column stores comma seperated numbers like '12,34,50'.
The query is:
Select * from TableName where col1 IN (12,34)
In the above query I am looking for numbers 12, 34 and 50. It works fine. Above query looks for 12 or 34 in the col1 . But I would like to search for both 12 and 34. Instead of using the OR condition I would like to use AND condition.
Any ideas?
Bhushan Andhare |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-13 : 08:54:47
|
the IN operator doesn't work the way you think it does. In actuality, it is just shorthand for this:
where col1 = 12 or col1 = 24
clearly a value of '12,34,50' will not satisfy either test. You probably want something like this:
where (col1 = 12 OR col1 like '12,%' or col1 like '%,12,% or col1 like '%,12')
and (col1 = 34 OR col1 like '34,%' or col1 like '%,34,% or col1 like '%,34')
|
 |
|
|
bhushan.andhare
Starting Member
3 Posts |
Posted - 2015-04-14 : 06:47:49
|
Hi gbritton,
Thanks for your reply.
I will explain it clearly.
I am passing the comma separated value as a parameter in a stored procedure. for example '12,34'
then in a stored procedure i would like to check if (col1 = 12 and col1=34 )
But as I am passing the value i would not know how many values I have passed.
I hope it helps you to understand my query.
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-14 : 07:15:01
|
You need a function that will SPLIT your comma delimited list and then compare it against the col1 delimited list
CREATE PROCEDURE MySProc
@MyValueList varchar(8000) -- e.g. '12,34'
AS
SELECT Col1, Col2
FROM MyTable
JOIN dbo.MySplitFunction(@MyValueList)
ON ',' + col1 + ',' LIKE '%,' + MySplitValue + ',%'
but it is a TERRIBLE way to do this job. You should have a CHILD TABLE with each individual value in the Col1 list and then you could just do
SELECT COl1, Col2
FROM MyParentTable AS P
JOIN MyChildTable AS C
ON C.ID = P.ID
WHERE C.Col1 IN (12, 34)
|
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-14 : 10:45:41
|
" i would like to check if (col1 = 12 and col1=34 ) "
Since that is clearly impossible, do you actually mean:
i would like to check if (col1 contains both 12 and 34 )
? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2015-04-14 : 12:13:35
|
It is called Relational Division.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
bhushan.andhare
Starting Member
3 Posts |
Posted - 2015-04-15 : 10:34:58
|
Hiii gbritton,
Yes I would like to check if (col1 contains both 12 and 34 )
where the col1 value is comma seperated numbers.
So i would like to check contains.
But I do not want to use IN clause as it checks for OR condition.
Thanks.
Bhushan Andhare |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-15 : 12:01:12
|
Use what I posted last week:
where (col1 = 12 OR col1 like '12,%' or col1 like '%,12,% or col1 like '%,12')
and (col1 = 34 OR col1 like '34,%' or col1 like '%,34,% or col1 like '%,34')
|
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-04-16 : 12:03:06
|
That may not work correctly.
You need to do this:
where ',' + col1 +',' like '%,12,%'
and ',' + col1 +',' like '%,34,%'
|
|
|
|
|
|
|
|
|
How to use AND condition in IN clause