MOD

# 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?

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

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
```

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.

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.

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

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.

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

MOD

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.

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

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.

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.

```SELECT SUBSTR(name, 1, 10) || '...' AS "Simple as that mister" FROM actors WHERE LENGTH(name) >= 10 ;
```

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

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. :)

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

and it works as required

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.

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

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

?

This was my solution:

```SELECT
CASE
WHEN LENGTH(name) > 10 THEN SUBSTR(name, 1, 10) || '...'
ELSE name
END AS "Names"
FROM actors
```

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

Great challenge question!

```SELECT REPLACE(name, 1, 10) || "..." AS truncated
FROM actors
WHERE LENGTH(truncated)> 12;
```

SELECT REPLACE(name, SUBSTR(name, 11, LENGTH(name)),"...") FROM actors ;

My answer: '''SQL SELECT SUBSTR(name, 1, 10)|| '...' AS name FROM actors; ''' I figured I didn't need a WHERE clause for this challenge.

PRO

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