Databases Reporting with SQL Working with Text Practice Session

Jonathan Grieve
MOD
Jonathan Grieve
Treehouse 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?

16 Answers

Steven Parker
Steven Parker
176,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;
Dwayne Pate
Dwayne Pate
12,247 Points

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

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

SELECT 
     SUBSTR(name, 1, 10) || "..." AS truncated_names
     FROM actors
Andrew Trachtman
Andrew Trachtman
3,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.

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,677 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.

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,738 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

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.

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

Jason Anders
MOD
Jason Anders
Treehouse 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.

:dizzy:

Michael Lefkowitz
Michael Lefkowitz
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 83,524 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.

Patriot Rika
Patriot Rika
3,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 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
176,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
Jonathan Grieve
Jonathan Grieve
Treehouse Moderator 83,524 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
13,218 Points

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

and it works as required

Stephanie Youstra
Stephanie Youstra
18,509 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.

vicente lee
vicente lee
6,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 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...

?

Sean Martin
Sean Martin
3,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 Gates
Kevin Gates
14,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 Gebru
Tommy Gebru
29,402 Points

Great challenge question!

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

Marcus Grant
PRO
Marcus Grant
Pro 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;

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;