[Solved] Problem using LIKE operator

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

[Solved] Problem using LIKE operator

Author

Topic

teslacoil
Starting Member

4 Posts

Posted - 2011-06-09 : 07:44:28

Hi everyone, after spending a lot of time searching for a solution, I'm still having a problem in one of my exercises.
This is what I have been asked to do:

*List name, type, price and description for all juices that include the word fruit but not fruits or fruit as part of another word (e.g. grapefruit).
This is the query I'm using:

SELECT juice_id, juice_name, juice_type, juice_price, juice_description
FROM tblJuice
WHERE juice_description LIKE '%fruit%'
AND juice_description NOT LIKE '%fruits%';

I cannot figure out how to not include fruit as part of another word.
Can anyone shed some light?
Thanks in advance.
xls file [url]http://www.mediafire.com/?b33t7f996yyir18[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-09 : 07:58:18

Tried this?

SELECT	juice_id,
	juice_name,
	juice_type,
	juice_price,
	juice_description
FROM	tblJuice
WHERE	' ' + juice_description + ' ' LIKE '% fruit %'

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-09 : 07:59:19

[code]SELECT juice_id,
juice_name,
juice_type,
juice_price,
juice_description
FROM tblJuice
WHERE CHARINDEX(' ' + juice_description + ' ', ' fruit ') > 0[/code]

N 56°04'39.26"
E 12°55'05.63"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-09 : 08:00:06

Consider the letter before and after fruit.
Note you can use [...] to check a sinbgle character.
[xyz] to mean any of xyz
[^xyz] to mean not any of xyz
[^a-z] to mean not between a and z

I take it you also need to cater for fruit being the first or last word or delimitted by punctuation.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

teslacoil
Starting Member

4 Posts

Posted - 2011-06-09 : 08:16:41

Hi nigelrivett and PESO, thanks for your reply
@nigelrivett
I have tried


WHERE juice_description LIKE '%fruit%'
AND juice_description NOT LIKE '%fruits%'
AND juice_description NOT LIKE '_%fruit%'
'^_fruit'
'_fruit'
'%_fruit%'
'_%fruit%'
'...%fruit%'
'%...fruit%'

and still the grapefruit is in the result
@ PESO
unfortunately both of your queries do not work, the first one I got 1305 Function CHARINDEX does not exist and the second one with no result.

Could anyone load this table and post the query? It will help me a lot.
Considering that the lesson for LIKE operator is:


1 Uses wildcards to test for pattern match
2 % represents any collection of characters
3 _ (underscore) represents any single character
Note searches using wildcards can be slow to process

and the example is:


List name and description of all juices where description includes ‘orange’
e.g.1
 SELECT juice_name, juice_description
FROM Juice
WHERE juice_description LIKE '%orange%‘
e.g. 2
WHERE name LIKE ‘T_m’  would return ‘Tim’, ‘Tom’ etc.

I believe I have to work out the solution by using only the syntax in the LIKE Operator from the code above.

Many thanks
S969

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-09 : 08:47:54

If you got "1305 Function CHARINDEX does not exist " then you are using MYSql. This is a MS SQL forum (T-SQL).
http://www.dbforums.com/mysql might be a better place to ask your question.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

teslacoil
Starting Member

4 Posts

Posted - 2011-06-09 : 08:51:06

@ DonAtWork
I see, sorry for my post, I did not relized it

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-09 : 09:08:38

Don't know if this works in mysql but it would be something like

WHERE juice_description LIKE '%[^a-zA-Z]fruit[^a-zA-Z]%'
or juice_description LIKE '%[^a-zA-Z]fruit'
or juice_description LIKE 'fruit[^a-zA-Z]%'
or juice_description = 'fruit'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

teslacoil
Starting Member

4 Posts

Posted - 2011-06-09 : 10:36:19

Thanks again nigelrivett but it didn't work, anyway I have realized that I had missed a space in '%fruit%' so to solve my original question
I had to run this query:

SELECT juice_id, juice_name, juice_type, juice_price, juice_description
FROM tblJuice
WHERE juice_description LIKE '% fruit %'

Thanks all for the help.
S969

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-09 : 15:32:14

See my previous comment - I think your query is probably wrong even though it returns the correct rows. You might find problem when trying it with fruits on that data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources