Databases Reporting with SQL Working with Text Practice Session

Tony Pitkin
PRO
Tony Pitkin
Pro 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
176,598 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
Tony Pitkin
Tony Pitkin
Pro Student 1,950 Points

What does the:

END AS truncated_name

do?

Steven Parker
Steven Parker
176,598 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

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.