IN Operator question 1

[JStevison]

Why does this not work?

select * from Menus where 'g' IN (Menus.menuSecurity)

Explanation: Menus.menuSecurity contains an nvarchar field with this data - a,b,c,d,e,f,g,h

I get 0 results. If I remove everything except the letter g I get a result of 1 record as expected.

 

[Andel]

Here's how:

select * from Menus where Menus.menuSecurity IN ('g')

Andel
andel@barroga.net

http://andyb.8m.com

 

[JStevison]

Thanks for the post. I get the same exact output either way. Any other suggestions.

 

[tlbroadbent]

That query doesn't work because Menus.menuSecurity is a single string of characters rather than multiple strings of single characters. You query is equivalent to the follwoing when it hits the row in question.

Select *
From Menus
Where 'g' IN ('a,b,c,d,e,f,g,h')

SQL tries to compare the 'g' to the single string of 8 letters separated by commas and gets no match.

The proper IN statement would be...

Select * From Menus
Where 'g' IN ('a','b','c','d','e','f','g',h')

You need to use the LIKE predicate, CharIndex or PatIndex to find a substring within a string.

Select * From Menus
Where menuSecurity Like '%g%'

Select * From Menus
Where CharIndex('g', menuSecurity)>0

Select * From Menus
Where PatIndex('%g%', menuSecurity)>0
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[JStevison]

That's what I was looking for. Thanks a million.