Jonathan GrieveTreehouse Moderator 83,524 Points
Final Question of the Playground Practice
Well done Chalkers, you got me on this one :)
This last one was very hard.
---> From the actors, truncate names greater than 10 charactor with ... e.g. William Wo... SELECT name from actors WHERE SUBSTR(LENGTH(name) > 10, name, "...")
My first thought was that involved the use of WHERE, SUBSTR and LENGTH and chaining these, not that we've done much of that in the course so far. But it returned no results.
Am I close?
Steven Parker176,604 Points
That's where UNION is handy...
SELECT SUBSTR(name, 1, 10) || "..." as Name FROM actors WHERE LENGTH(name) > 10 UNION SELECT name FROM actors WHERE LENGTH(name) <= 10;
Wasn't asked to exclude names, so simply truncated everything
SELECT SUBSTR(name, 1, 10) || "..." AS truncated_names FROM actors
Andrew Trachtman3,677 Points
I think I got this one correct with only the things Andrew taught us.
-- I REPLACE the name text. I go from the substring position 11 out to 999 and replace it all with "..." --Obviously this fails with names that exceed 999 characters. SELECT REPLACE(name, SUBSTR(name, 11, 999), "..." ) FROM actors;
If anyone else wants to check this, let me know if it works for you. When I tested it, it returns all names. Those that exceed 10 characters have the ellipses and those that do not are displayed normally.
This one was tricky, indeed. Thanks for the above hints. I solved it using a variant of SUBSTR():
SELECT id, REPLACE(name,SUBSTR(name,10),"...") AS "truncated_names" FROM actors;
However, I hope that in a future lesson we will learn how to display names equal or less than 10 chars, and for those greater than 10 chars... to truncate names to 7 chars + "..." so that the final output does not exceed 10 chars, either. Think about displaying results in a field that has exactly 10 chars. :D
Jason AndersTreehouse Moderator 144,813 Points
I've got it where it returns all the names longer than 10 characters truncated with the
..., but now it won't return any names shorter than 10? Is this what Andrew wants? Or are ALL the names supposed to return with just the longer ones truncated?
Let me know Jonathan Grieve if you want me to post this query.
Patriot Rika3,738 Points
It asks to truncate names greater than 10 characters , which means
SELECT SUBSTR(name, 1 , 10) || " ..." FROM actors; is enough , because you have already set the length to 10 , after that length you will get "..." dots as required.
Joacim Alexandar Bruun3,539 Points
SELECT SUBSTR(name, 1, 10) || '...' AS "Simple as that mister" FROM actors WHERE LENGTH(name) >= 10 ;
Steven Parker176,604 Points
Here's a few hints:
- Your SUBSTR will be part of the SELECT clause
- the length test will be part of the WHERE clause
- SUBSTR arguments are string, starting position, and (optional) length
Ran Su13,218 Points
my answer : UPDATE actors SET name = REPLACE(name,SUBSTR(name,11,50),"...");
and it works as required
vicente lee6,270 Points
Steven Parker's comment should be the right answer here, for those wondering how to show length(name) <= 10 with no truncation AND length(name) > 10 with truncation on the same query. That said, I don't think Chalkers intended for this query to be this "difficult" given that we haven't seen UNION. When you do a SELECT and WHERE, it will show only those that meet the condition set by the WHERE clause, hence the name "SELECT".
Ethan Kingsley1,545 Points
Is there a definitive answer as to what the expected output is using only the tools available to us up til this point?
Is the best we can get
id 13, Aadil... id 14, Aakash Dabh...
Sean Martin3,430 Points
This was my solution:
SELECT CASE WHEN LENGTH(name) > 10 THEN SUBSTR(name, 1, 10) || '...' ELSE name END AS "Names" FROM actors
Kevin Gates14,707 Points
Based on the question:
--- From the actors, truncate names greater than 10 charactor with ... e.g. William Wo...
It doesn't ask us to return all names, but to only truncate names with greater than 10 characters. In that case, I believe this is correct:
SELECT SUBSTR(name, 1, 10) || '...' AS truncated_name FROM actors WHERE LENGTH(name) > 10;
Tommy Gebru29,402 Points
Great challenge question!
SELECT REPLACE(name, 1, 10) || "..." AS truncated FROM actors WHERE LENGTH(truncated)> 12;
Piotr Mozgawa4,065 Points
What about this? I think it works. Instead of adding "..." replace all characters from 11th until the end of the text ( LENGTH(name) )
SELECT REPLACE(name, SUBSTR(name, 11, LENGTH(name)),"...") FROM actors ;