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 trial

Databases Reporting with SQL Working with Text Practice Session

Jonathan Grieve
MOD
Jonathan Grieve
Treehouse Moderator 91,253 Points
Posted by Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 91,253 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?

20 Answers

Steven Parker
Steven Parker
231,272 Points
Steven Parker
Steven Parker
231,272 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;
Dwayne Pate
Dwayne Pate
12,249 Points
Dwayne Pate
Dwayne Pate
12,249 Points

This gives me the result that seems to be in the sprit of what was asked. Thanks for suggesting UNION!

Andrew Trachtman
Andrew Trachtman
3,680 Points
Andrew Trachtman
Andrew Trachtman
3,680 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.

Ethan Kingsley
Ethan Kingsley
1,545 Points
Ethan Kingsley
Ethan Kingsley
1,545 Points

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.

Andrew Trachtman
Andrew Trachtman
3,680 Points
Andrew Trachtman
Andrew Trachtman
3,680 Points

I 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.

Cristian Glodeanu
Cristian Glodeanu
4,031 Points
Cristian Glodeanu
Cristian Glodeanu
4,031 Points

Yes,

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
Patriot Rika
3,739 Points
Patriot Rika
Patriot Rika
3,739 Points

Cristian 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
Ethan Kingsley
1,545 Points
Ethan Kingsley
Ethan Kingsley
1,545 Points

This 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
John Fujita
1,776 Points

The 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
jamesnorberto
5,968 Points
jamesnorberto
jamesnorberto
5,968 Points

Wasn't asked to exclude names, so simply truncated everything

SELECT 
     SUBSTR(name, 1, 10) || "..." AS truncated_names
     FROM actors
Patriot Rika
Patriot Rika
3,739 Points
Patriot Rika
Patriot Rika
3,739 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.

Jason Anders
MOD
Jason Anders
Treehouse Moderator 145,860 Points
Jason Anders
Jason Anders
Treehouse Moderator 145,860 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.

:dizzy:

lfkwtz
lfkwtz
12,295 Points

I'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
Jonathan Grieve
Treehouse Moderator 91,253 Points
Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 91,253 Points

Hi 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
vicente lee
6,271 Points
vicente lee
vicente lee
6,271 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".

Joacim Alexandar Bruun
Joacim Alexandar Bruun
3,539 Points
Joacim Alexandar Bruun
Joacim Alexandar Bruun
3,539 Points 
SELECT SUBSTR(name, 1, 10) || '...' AS "Simple as that mister" FROM actors WHERE LENGTH(name) >= 10 ;
Steven Parker
Steven Parker
231,272 Points
Steven Parker
Steven Parker
231,272 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
Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 91,253 Points
Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 91,253 Points

Yes 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
Ran Su
16,056 Points
Ran Su
Ran Su
16,056 Points

my answer : UPDATE actors SET name = REPLACE(name,SUBSTR(name,11,50),"...");

and it works as required

Stephanie Youstra
Stephanie Youstra
18,513 Points

To 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
Sean Martin
3,431 Points
Sean Martin
Sean Martin
3,431 Points

This was my solution:

SELECT
        CASE 
          WHEN LENGTH(name) > 10 THEN SUBSTR(name, 1, 10) || '...'
          ELSE name
        END AS "Names"
          FROM actors
Ethan Kingsley
Ethan Kingsley
1,545 Points
Ethan Kingsley
Ethan Kingsley
1,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...

?

Kevin Gates
Kevin Gates
15,053 Points
Kevin Gates
Kevin Gates
15,053 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 Gebru
Tommy Gebru
30,164 Points
Tommy Gebru
Tommy Gebru
30,164 Points

Great challenge question!

SELECT REPLACE(name, 1, 10) || "..." AS truncated
FROM actors
WHERE LENGTH(truncated)> 12;
Piotr Mozgawa
Piotr Mozgawa
5,937 Points
Piotr Mozgawa
Piotr Mozgawa
5,937 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 ;

Noah Palm
Noah Palm
12,443 Points
Noah Palm
Noah Palm
12,443 Points

My 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
PLUS
Jordan young
Courses Plus Student 5,277 Points
Jordan young
Jordan young
Courses Plus Student 5,277 Points

Hi... 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
Ana Lopez
1,177 Points
Ana Lopez
Ana Lopez
1,177 Points

SELECT 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
PLUS
Mary Miller
Courses Plus Student 2,451 Points
Mary Miller
Mary Miller
Courses Plus Student 2,451 Points

I did it this way:

SELECT SUBSTR("name", "0", "10") || " " || "..." AS "Marry Miller" FROM actors WHERE LENGTH(name) > 10;

Marcus Grant
PLUS
Marcus Grant
Courses Plus Student 2,546 Points
Marcus Grant
Marcus Grant
Courses Plus Student 2,546 Points

So 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
barbarak
36,789 Points

No 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;