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

Posted by tonypitkin2
tonypitkin2
Courses Plus Student 1,950 Points

Can you combine a Substring and Replace function inside one another?

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

What I want to do only add the "..." IF the string exceeds 10 characters. Is this possible? I have this as my starting point:

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

However, I noticed for names of 10 or less letters I still get the "..." added afterwards. I'd like to conditionally avoid that if possible...

2 Answers

Steven Parker
Steven Parker
230,178 Points
Steven Parker
Steven Parker
230,178 Points

This would be a good place for a CASE statement.

Has the course covered the use of CASE yet? You could use it in something like this:

SELECT CASE WHEN LENGTH(name) > 10
            THEN SUBSTR(name, 1, 10) || "..."
            ELSE name
       END AS truncated_name
FROM actors
tonypitkin2
tonypitkin2
Courses Plus Student 1,950 Points

What does the:

END AS truncated_name

do?

Steven Parker
Steven Parker
230,178 Points
Steven Parker
Steven Parker
230,178 Points

Every CASE statement must have an "END". These two keywords act as the "bookends" of the conditional.

Then the "AS truncated_name" applies an alias to the conditional column, so it will be displayed as "truncated_name". Without an alias, the column's name would be the entire CASE statement (rather ugly!).

Mohammad Bazarbay
Mohammad Bazarbay
1,967 Points
Mohammad Bazarbay
Mohammad Bazarbay
1,967 Points

To avoid having '...' with names that are under than 10 of length, I did the following:

SELECT id, name, SUBSTR(name, 1, 10) || '...' AS too_long FROM actors WHERE LENGTH(name) > 10;

I am sure there are other ways around it but this is how i I did it. I hope this helps. let me know if you have questions.