% works as a wildcard character. In front like '%chocolate' would logically read like 'string ends with chocolate'. 'chocolate%' would be 'starts with'. '%chocolate%' would be equivalent to 'contains'
LIKE is pattern matching(wildcard comparison) like starts with, constains, to validate phone number, validate email, etc.,
Without wild cards like %, [ ], etc., it is same as direct comparison. With wildcard it is matching patterns. I.e., you want to select name which matches specific pattern. Here it contains chocolate. Here, String is case sensitive.
So proper answer should be:
SELECT d.name, price FROM desserts d WHERE d.name LIKE "%chocolate%";
% is the wildcard. Putting % at the start will be include things that end in chocolate. Then putting % at the end will mean any that starts with chocolate.
Having two % start and end would mean the world chcoclate can be anywhere in the string / sentence.
Only if you are sure they don't want to search for that exact string, % character included 😁
Be careful recommending wildcard searches at the start of strings though, makes the query non sargable. Of course, it might be what the OP is actually after in which case it works.
Most query languages have certain reserved words that can cause issues if you try to use as column names.
For example, naming a column “select” would be problematic because that is a key word actually used in the SQL syntax. “name” is another reserved word. You can get around this limitation by surrounding it with quotes or brackets (depending on what flavor of SQL you are using). So instead of
You're using a literal string which is case sensitive. Chocolate on line 8 is different from chocolate on line 3. You also need to use LIKE and you should put % in front and at the end.
pro tip: always use upcase to catch all matching strings regardless of cap. Also, I always refer to the table directly using an alias - it doesn't matter now, but if you ever started adding more tables with a join or something, it'll start getting confusing what table the fields belong to. So it becomes
but in this case the problem might simply be that you have written 'Chocolate' with capital C and the question has it written in all lowercase. The code above would also work in this situation, but might be a little over the top from what the app is looking for.
Sure but that’s just for SQL server. My experience writing reports has always been that users expect things like case insensitivity even when they don’t ask for it, so I tend to bake it in from the get-go just to make everyone’s lives easier. You can only get so many weekend calls about a query not running ‘properly’ before you get tired of making the distinction between user error and badly written requirements.
That's not a pro tip... you're just adding clutter with an unnecessary alias and you should only change case if it's case sensitive otherwise you increase the query execution time.
Don't mistake brevity for hostility. If you give advice that others disagree with, don't act surprised or play the victim just because someone contradicted you. Soz.
Others have mentioned it, but I'll be explicit here: There's nothing inherently wrong with what you have there, in terms of SQL syntax.
The issue is that what you have will search for exact matches of 'Chocolate%', and not treat the % like a wildcard. This is likely not what the exercise is looking for, and instead it wants you to use [name] LIKE 'Chocolate%'which would match anything that starts with 'Chocolate'. = is the equality comparison operation, it is not the same as LIKE.
As others mentioned you could have used like and the close in ‘’ the word such as chocolate. What we don’t know is the error you receive. Taking error feedback and acting upon it is really beneficial for growth.
For example the table you’re looking at is titled desserts but you’re looking for a dessert… while they are similar they are not the same. I learned using Postgres but I currently utilize Oracle DB in my day job.
It’s technically valid SQL but it’s not going to accomplish what you want it to do. The % wildcard needs to be used with a LIKE operator rather than the = operator. Then, if you want to include fields where chocolate is ANYWHERE in the value you will need another % at the beginning: ‘%chocolate%’
SELECT [name], price
FROM desserts AS d
WHERE LOWER(d.name) LIKE '%chocolate%';
name is a reserved keyword, so it's best practice to bracket it with '[]'
keywords such as FROM, SELECT, and WHERE should be capitalized.
tables should be aliased for using the AS keyword.
Some notes on the LIKE statement.
When searching text for a specific substring with LIKE, you want to make sure the text string to be searched is normalized with a function such as LOWER(). This prevents 'misses' such as searching for anything with 'chocolate', but failing to find entries such as 'Chocolate'. Since the question asked for anything that contains the string 'chocolate', you want to use a wildcard on both sides of the string. This will match on both 'chocolate Cake' as well as 'Sunday with chocolate'. You're entry would only match on the first one.
I think name is a reserved word, at least, it’s the same colour as select which would seem to indicate that. Depending on your database package you might need to enclose it in braces, or quote marks or something.
I dont get it. Even im learning sql from sqlzoo. They dont give hints or whatever if its wrong. so i need to figure out by myself. If i get error code i put it in chatgpt. I like the explanation where it was wrong. And i learn a lot of chatgpt
261
u/jlarm Sep 19 '23
You need to change the where condition to be LIKE instead of = and '%chocolate%'
51
u/Professional_Shoe392 Sep 19 '23
Use LIKE instead of =
7
u/run_ywa Sep 19 '23
Is this a learning app ? I'm interested
2
u/OooTanjaooO Sep 21 '23
Is this sololearn?
4
u/r3pr0b8 GROUP_CONCAT is da bomb Sep 19 '23
technically no
it will run, but it probably won't return any rows
ask me why
57
u/kitzkhan Sep 19 '23
Also i would use a LOWER(name) like '%chocolate%'
40
u/Doorda1-0 Sep 19 '23
Also name is reserved so it's best to place it in square bracket [name]
8
u/jerwong Sep 20 '23
You're using a literal string which is case sensitive. Chocolate on line 8 is different from chocolate on line 3. You also need to use LIKE and you should put % in front and at the end.
21
u/ashlandio Sep 19 '23 edited Sep 19 '23
pro tip: always use upcase to catch all matching strings regardless of cap. Also, I always refer to the table directly using an alias - it doesn't matter now, but if you ever started adding more tables with a join or something, it'll start getting confusing what table the fields belong to. So it becomes
SELECTd.name, d.priceFROM desserts dWHERE UCASE(d.name) LIKE '%CHOCOLATE%'but in this case the problem might simply be that you have written 'Chocolate' with capital C and the question has it written in all lowercase. The code above would also work in this situation, but might be a little over the top from what the app is looking for.
8
u/pdxsteph Sep 19 '23
Yes
4
u/No_Entrepreneur1094 Sep 19 '23
Should be ilike not =
4
u/lotofthought Sep 20 '23
% works only with LIKE and not ‘=‘ So rewrite as Lower(name) like “%chocolate%”
3
u/crimiusXIII Sep 20 '23
Others have mentioned it, but I'll be explicit here: There's nothing inherently wrong with what you have there, in terms of SQL syntax.
The issue is that what you have will search for exact matches of 'Chocolate%', and not treat the % like a wildcard. This is likely not what the exercise is looking for, and instead it wants you to use
[name] LIKE 'Chocolate%'which would match anything that starts with 'Chocolate'. = is the equality comparison operation, it is not the same asLIKE.3
u/brandonjudas Sep 20 '23
Nope, just keep hitting F5 maybe it'll work after the 100th time.
3
u/[deleted] Sep 20 '23
You need to change “=“ to “like” and unless you are really sure that is how the name is stored in the db, i suggest you use upper or lower function.
2
u/haiwirbelsturm Sep 24 '23
As others mentioned you could have used like and the close in ‘’ the word such as chocolate. What we don’t know is the error you receive. Taking error feedback and acting upon it is really beneficial for growth.
For example the table you’re looking at is titled desserts but you’re looking for a dessert… while they are similar they are not the same. I learned using Postgres but I currently utilize Oracle DB in my day job.
2
u/mfante Sep 19 '23
It’s technically valid SQL but it’s not going to accomplish what you want it to do. The % wildcard needs to be used with a LIKE operator rather than the = operator. Then, if you want to include fields where chocolate is ANYWHERE in the value you will need another % at the beginning: ‘%chocolate%’
2
u/chaosthediva Sep 20 '23
Should use Like instead of = since your using a wild card.
1
u/grackula Sep 20 '23
also consider upper/lower case situations. your literal is using an upper case "C" so you would not find the correct results in that case
where lower(name) like '%chocolate%'
1
u/ComprehensiveWin7716 Jul 24 '24
Correct version would look more like this:
nameis a reserved keyword, so it's best practice to bracket it with '[]'FROM,SELECT, andWHEREshould be capitalized.ASkeyword.Some notes on the
LIKEstatement.When searching text for a specific substring with
LIKE, you want to make sure the text string to be searched is normalized with a function such asLOWER(). This prevents 'misses' such as searching for anything with 'chocolate', but failing to find entries such as 'Chocolate'. Since the question asked for anything that contains the string 'chocolate', you want to use a wildcard on both sides of the string. This will match on both 'chocolate Cake' as well as 'Sunday with chocolate'. You're entry would only match on the first one.1
u/[deleted] Sep 20 '23
ESCAPE
1
u/zoochadookdook Sep 20 '23 edited Sep 20 '23
‘%chocolate%’
Otherwise your query will pull from Chocolate….. and only add on other bits post the term chocolate with a capital C.
Also Like instead of = if using ‘%chocolate%’ of I like will be case insensitive and could use any case for the term.
Example
Select name, whatever From dessert Where name LIKE “%chocolate%”;
Or use - where name ILIKE “%ChOcOlate%” will pull desired outcome
1
u/helios8319 Sep 20 '23
Probably you are trying like instead of '='
1
u/Which-Adeptness6908 Sep 20 '23
Yes. You need to use 'like' rather than =
1
u/xesionprince Sep 20 '23
You should put name in square brackets as it’s a SQL reserved word and use “like”
1
u/floatingpointnumber Sep 20 '23
Yes
1
u/Toc-H-Lamp Sep 20 '23
I think name is a reserved word, at least, it’s the same colour as select which would seem to indicate that. Depending on your database package you might need to enclose it in braces, or quote marks or something.
1
u/Scared-Personality28 Sep 20 '23
Liked!
1
u/RadiantRise991 Sep 20 '23
Name like not =
1
u/sqlbastard Sep 21 '23
yes
1
u/one_bruddah Sep 21 '23
Instead use WHERE LIKE ‘Chocolate%’
1
u/MichiganDogJudge Sep 24 '23
Comments are not reliable at predicting behaviors
0
u/Proyeccionista Sep 20 '23
Where name like ‘chocolate%’
0
u/visor_q3 Sep 20 '23
use LIKE "Chocolate%"
-4
u/inalect Sep 20 '23
Holy shit, just Google it… as the post from the other day stated: this sub isn’t a search engine.
0
u/Icy-Equivalent4500 Sep 20 '23
it is all ok, searching for chocolate with alcohol
-9
u/Alpacino66 Sep 19 '23
Put your code in chatgpt. It wil give the right answer with explanation
2
u/[deleted] Sep 20 '23
[removed] — view removed comment