[thelist] SQL: Searching a field with mutiple names

Warden, Matt mwarden at mattwarden.com
Thu Aug 2 10:19:28 CDT 2001


On Aug 2, Ornstein, Ian had something to say about RE: [thelist] SQL: ...

>or
>SELECT * FROM SystemPages 
>WHERE   PageContent in ('wombat','toothpaste','eggplant')
>
>The in clause can be populated by another SELECT statement.


This actually isn't the same thing at all. You're testing total equality
rather than partial matches. He was testing things like:

PageContent LIKE '%foo%' OR PageContent LIKE '%bar%'

and you are trying to use the following as a substitute:

PageContent in ('foo','bar')

in is actually a different way to set OR equalities. So, what you have
above is actually:

PageContent='foo' or PageContent='bar'


However, I think you were thinking that 'in' meant that it matches if the
stuff between (...) is found in the field PageContent. Fairly common
mistake when dealing with text fields, but it just ain't right. Let's use
numerics for the sake of demonstration:


select 'hello'
from foo
where 1 in (1,2)

expanded, this means:

select 'hello'
from foo
where 1=1
or 1=2

See how 'in' is OR'd equalities?

And I don't think this would work:

select * from SystemPages
where PageContent in ('%foo%','%bar%')

in fact, i know that won't work because that would be saying:

select * from SystemPages
where PageContent='%foo%'
OR PageContent='%bar%'

not sure you can do what you're trying to do with LIKEs.


--
mattwarden
mattwarden.com





More information about the thelist mailing list