Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trialJonathan Grieve
Treehouse Moderator 91,253 PointsFinal 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?
20 Answers
Steven Parker
231,184 PointsThat'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;
Andrew Trachtman
3,680 PointsI 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.
Ethan Kingsley
1,545 PointsI thought this looked good but it caused some weird behavior with longer first names, e.g. id 44 (Abraham Abraham) became "Ab... Ab..." with this code. I'm not sure why though because Abraham is not more than 11 characters.
Andrew Trachtman
3,680 PointsI think the issue is not the length of the name, but the fact that the first and last names are the same. This results in:
//This is just to illustrate what is happening.
1 2 3 4 5 6 7 8 9 10
A B R A H A M _ A B R A H A M
So Abraham Ab... ends up becoming Ab... Ab... because after the 10th character it sees "raham" and ends up replacing every instance of "raham" that it sees with "..." which includes the first part of the name.
You can confirm this by looking at ID 52. Adam Bareham becomes Ad... Bareh... because it sees that at position 11 we have "am -----" and so it replaces all instances of "am" with "..."
I'm not sure that it's possible to overcome this bug with just the things we learned in this course.
Ethan Kingsley
1,545 PointsAh, I see, Thanks
Cristian Glodeanu
4,031 PointsYes,
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
Patriot Rika
3,739 PointsCristian Glodeanu your answer might be a solution for this one , but it will show dots after the 9th character , it says truncate name greater than 10 characters which means including 10th character. Also making simple queries complex may result in bad performance. Try to keep queries as simple as possible.
But testing solutions like yours proves that you have a very good knowledge of functions and keywords. When you are in learning phase you can try everything.
Ethan Kingsley
1,545 PointsThis code has the same issue as Andrew Trachtman 's code, where longer first names get strangely truncated.
From my other post:
I thought this looked good but it caused some weird behavior with longer first names, e.g. id 44 (Abraham Abraham) became "Ab... Ab..." with this code. I'm not sure why though because Abraham is not more than 11 characters.
John Fujita
1,776 PointsThe reason this answer and Andrew Tachman's answer is wrong is because of the nested SUBSTR in the REPLACE.
First the SUBSTR: this is part is saying that it will return the the rest of the name after the 10th character until it terminates.
Then the REPLACE is going to look at the names and if anywhere in the actor's name the characters that were returned by the SUBSTR are present in the name it will be replaced with "...".
So in the case of Abraham Abraham, the characters returned by the SUBSTR will be "raham" because that's the rest of the name starting after the 10th character. The REPLACE sees that "raham" needs to be replaced with a "...". This is why you'll be returned with Ab... Ab...
This will happen with any other names that share this repeating characteristic. This is not a bug and is working exactly as intended because of the way you wrote it.
For the record, I went with this as my answer:
SELECT SUBSTR(name, 1, 10) || "..." AS "truncated_names" FROM actors;
Yeah, this will put the ellipses after names that are shorter than 10 characters, but I assume we'll learn later on how to do this using CASE or something similar.
This is also probably super late in relation to when this question was written but I'm still making my way through :P
jamesnorberto
5,968 PointsWasn't asked to exclude names, so simply truncated everything
SELECT
SUBSTR(name, 1, 10) || "..." AS truncated_names
FROM actors
Patriot Rika
3,739 PointsIt 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.
Jason Anders
Treehouse Moderator 145,860 PointsI'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.
lfkwtz
12,295 PointsI'm curious about this too. I used:
SELECT SUBSTR(name, 1, 10) || "..." FROM actors WHERE LENGTH(name) > 10;
But I'd like to know how I could include names that are 10 or less into my results.
Jonathan Grieve
Treehouse Moderator 91,253 PointsHi Jason,
I've just realised i left this question unanswered :)
I tried it again with this
SELECT name, SUBSTR(name, 1, 10) || "..." FROM actors WHERE LENGTH(name) > 10;
But it affects all the records. I expected to find a smaller second row for this query but even names smaller than 10 are affected by the substring.
vicente lee
6,271 PointsSteven 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".
Joacim Alexandar Bruun
3,539 PointsSELECT SUBSTR(name, 1, 10) || '...' AS "Simple as that mister" FROM actors WHERE LENGTH(name) >= 10 ;
Steven Parker
231,184 PointsHere'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
Jonathan Grieve
Treehouse Moderator 91,253 PointsYes a lightbulb was just beginning to switch on about SUBSTRING, and then I could make concatenate the ... ellipsis onto the string that's left. But then I still need length to determine which of the rows to add the ellipsis to. I'll have a crack at this later when I've had time to give my brain a rest. :)
Ran Su
16,056 Pointsmy answer : UPDATE actors SET name = REPLACE(name,SUBSTR(name,11,50),"...");
and it works as required
Stephanie Youstra
18,513 PointsTo Ran Su 's comment ..... be careful using "UPDATE" like that, as you have now changed the contents of your database, and will not be able to get the actual names of the actors anymore. REPLACE only changes what you see; UPDATE changes the actual database.
Sean Martin
3,431 PointsThis was my solution:
SELECT
CASE
WHEN LENGTH(name) > 10 THEN SUBSTR(name, 1, 10) || '...'
ELSE name
END AS "Names"
FROM actors
Ethan Kingsley
1,545 PointsIs 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...
?
Kevin Gates
15,053 PointsBased 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 Gebru
30,164 PointsGreat challenge question!
SELECT REPLACE(name, 1, 10) || "..." AS truncated
FROM actors
WHERE LENGTH(truncated)> 12;
Piotr Mozgawa
5,937 PointsWhat 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 ;
Noah Palm
12,443 PointsMy answer: '''SQL SELECT SUBSTR(name, 1, 10)|| '...' AS name FROM actors; ''' I figured I didn't need a WHERE clause for this challenge.
Jordan young
Courses Plus Student 5,277 PointsHi... uh, lots of comments here.
I think my code did what the challenge was, although it sounds like i'm missing something.
SELECT SUBSTR(name, 1, 10)||"..." FROM actors;
Am I wrong??
Ana Lopez
1,177 PointsSELECT replace(name, substr(name, 11, length(name)), "..."), length(name) FROM actors;
This is my solution, the second column that I retrieve "length(name)" is to check the results. If I have understood right, you get all the names but just the ones longer than 10 gets the "...".
Mary Miller
Courses Plus Student 2,451 PointsI did it this way:
SELECT SUBSTR("name", "0", "10") || " " || "..." AS "Marry Miller" FROM actors WHERE LENGTH(name) > 10;
Marcus Grant
Courses Plus Student 2,546 PointsSo many of you over complicating it lol.
Like John Fujita and jamesnorberto said it is as simple as:
SELECT SUBSTR(name, 1, 10) || "..." AS "truncated_names" FROM actors;
barbarak
36,789 PointsNo it isn't because you're adding '...' to names less than 10 characters as well when they aren't truncated.
Mine similar to Sean's:
SELECT CASE WHEN length(name) > 10 THEN (substr(name, 1, 10) || '...') ELSE name END AS truncated_name FROM actors;
Dwayne Pate
12,249 PointsDwayne Pate
12,249 PointsThis gives me the result that seems to be in the sprit of what was asked. Thanks for suggesting UNION!