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

Posted by Michael Cook
.a{fill-rule:evenodd;}techdegree seal-36
Michael Cook
Full Stack JavaScript Techdegree Graduate 28,975 Points

How to return all names in a table, and truncate only names greater than 10 characters?

This is the challenge: 

From the actors, truncate names greater than 10 characters with ... e.g. William Wo...

There an actors table with an id column and a name column.

I can't figure out how to apply the concatenation of the "..." to the end of only names longer than 10 characters. If a name is less than 10, I just want the name to display. If the name is greater than 10, it should be truncated and have ... concatenated to the end. This is my query:

SELECT SUBSTR(name, 1, 11) || "..." FROM actors;

But this produces results like this:

Johnny Appl... Jane Doe...

So how can I run a query can will return all names, and truncate the long ones but not alter the short ones? Thanks.

Michael Cook
.a{fill-rule:evenodd;}techdegree seal-36
Michael Cook
Full Stack JavaScript Techdegree Graduate 28,975 Points

I can't give more info than that really. The challenge comes from the SQL playground rather than one of the interactive course challenges.

Michael Cook
.a{fill-rule:evenodd;}techdegree seal-36
Michael Cook
Full Stack JavaScript Techdegree Graduate 28,975 Points

Here is the link to the SQL playground session https://teamtreehouse.com/sql_playgrounds/372#/queries/9188815b-6faf-44ae-8126-50355333e842

1 Answer

Steven Parker
Steven Parker
231,007 Points
Steven Parker
Steven Parker
231,007 Points

As a generic hint: this might be a good place to use a CASE expression so you can return different results based on the LENGTH of the name.

If that doesn't help you solve it, please provide a link to the page you are working with and I can be more specific.

Michael Cook
.a{fill-rule:evenodd;}techdegree seal-36
Michael Cook
Full Stack JavaScript Techdegree Graduate 28,975 Points

I am only on the Reporting with SQL course so I haven't learned CASE yet.

Steven Parker
Steven Parker
231,007 Points
Steven Parker
Steven Parker
231,007 Points

In that case (pun intended) you could create two different queries, one to show only the short names unmodified, and another to show the longer ones truncated — and then combine them with a UNION.

If you'd like to see a complete solution, it turns out I answered a similar question (and for a moderator!) about three years ago.