f



Like '' & '' & ''

Having this description column  in a table

"Cookies, peanut butter sandwich, regular"


When using this WHERE clause:

	WHERE (((Food_Data.Description) Like "*Butter*" & "*Peanut*"));

I dont get any results, however when using it this way round

	WHERE (((Food_Data.Description) Like "*Peanut*" & "*Butter*"));

I get the row as expected.

I assume that the query looks for them in order.

Is there a way i can get around this and make the query search for both 
keywords no matter which order i ype them in.

Thanks
Dave

0
7/23/2003 11:23:59 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

3 Replies
1421 Views

Similar Articles

[PageSpeed] 57

[posted and mailed]

SkunkDave (dave_casserly@totalise.co.uk) writes:
> Having this description column  in a table
> 
> "Cookies, peanut butter sandwich, regular"
> 
> 
> When using this WHERE clause:
> 
>      WHERE (((Food_Data.Description) Like "*Butter*" & "*Peanut*"));
> 
> I dont get any results, however when using it this way round
> 
>      WHERE (((Food_Data.Description) Like "*Peanut*" & "*Butter*"));
> 
> I get the row as expected.
> 
> I assume that the query looks for them in order.

The query looks for a string that matches the pattern "*Peanut**Butter*",
so obviously it's not going to match "Butter Peanut".

As suggested by Harald Albrech this works:

  WHERE (Food_Data.Description Like "*Butter*" or 
         Food_Data..Description Like "*Peanut*");

Note that the newsgroup you posted is devoted to MS SQL Server, and
the syntax you posted is not legal in SQL Server, but it looks that
it could be Access. Access has its own newsgroup, I believe the name is 
comp.databases.ms-access. You may want to post there next time you
have a problem.

-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
sommar (1290)
7/24/2003 8:41:45 AM
Erland Sommarskog wrote:
> [posted and mailed]
> 
> SkunkDave (dave_casserly@totalise.co.uk) writes:
> 
>>Having this description column  in a table
>>
>>"Cookies, peanut butter sandwich, regular"
>>
>>
>>When using this WHERE clause:
>>
>>     WHERE (((Food_Data.Description) Like "*Butter*" & "*Peanut*"));
>>
>>I dont get any results, however when using it this way round
>>
>>     WHERE (((Food_Data.Description) Like "*Peanut*" & "*Butter*"));
>>
>>I get the row as expected.
>>
>>I assume that the query looks for them in order.
> 
> 
> The query looks for a string that matches the pattern "*Peanut**Butter*",
> so obviously it's not going to match "Butter Peanut".
> 
> As suggested by Harald Albrech this works:
> 
>   WHERE (Food_Data.Description Like "*Butter*" or 
>          Food_Data..Description Like "*Peanut*");
> 
> Note that the newsgroup you posted is devoted to MS SQL Server, and
> the syntax you posted is not legal in SQL Server, but it looks that
> it could be Access. Access has its own newsgroup, I believe the name is 
> comp.databases.ms-access. You may want to post there next time you
> have a problem.
> 


Ive used the query suggested except ive used the word 'and' instead of 
'or'. Works fine.

Yes the query is MS Access. I didnt see the Access newsgroup. I would 
have posted it there if i did.

Out of curiousity, what would be the syntax for SQL Server?

Cheers

0
7/24/2003 10:40:04 AM
SkunkDave (dave_casserly@totalise.co.uk) writes:
> Out of curiousity, what would be the syntax for SQL Server?

 WHERE (Food_Data.Description Like '%Butter%' or    
        Food_Data.Description Like '%Peanut%');

OR or AND depends on the desired result when only one string is included.



-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
sommar (1290)
7/24/2003 8:55:22 PM
Reply: