String Functions - Learning MySQL and MariaDB - page 215

It may be difficult to see, but we're enclosing the character that we want trimmed — a
double quote — within single quotes. The keyword BOTH isn't actually necessary because
it's the default — that's why we didn't specify it in the previous example. If you don't
want to remove the string given from one end or the other, you can specify LEADING or
TRAILING , thus making TRIM() work like LTRIM() or RTRIM() . The default string
to trim is a space, as we have seen.
When displayingdata in web forms and other such settings, it's sometimes useful to pad
the data displayed with dots or some other filler. This can be necessary when dealing with
VARCHAR columns where the width varies. Padding the results of a column selected can
help the user to see the column limits. There are two functions that may be used forpad-
ding: LPAD() and RPAD() . There is also SPACE() , whichpads the string with spaces:
SELECT CONCAT(RPAD(common_name, 20, '.' ),
RPAD(Families.scientific_name, 15, '.'),
Orders.scientific_name) AS Birds
FROM birds
JOIN bird_families AS Families USING(family_id)
JOIN bird_orders AS Orders
WHERE common_name != ''
AND Orders.scientific_name = 'Ciconiiformes'
ORDER BY common_name LIMIT 3;
+--------------------------------------------------+
| Birds |
+--------------------------------------------------+
| Abbott's Babbler....Pellorneidae...Ciconiiformes |
| Abbott's Booby......Sulidae........Ciconiiformes |
| Abbott's Starling...Sturnidae......Ciconiiformes |
+--------------------------------------------------+
Notice how all the bird families and orders are aligned vertically. This is because we pad-
ded each value out to its maximum width using RPAD() . The first argument was the
column to read, the second was the total size of the resulting string we want, and the third
was a period so that periods apear for columns that have less text. This happens to work
because MySQL uses a fixed-width font. We could uses spaces instead of dots for a simil-
ar effect. For web display, we might use   as paddingelement for non-breaking
spaces.