Database Reference
In-Depth Information
Formatting Strings
Several string functions canformat or reconstitute text for a better display. They allow you
to store data in columns in a raw form or in separate components and then create the dis-
play you want when you retrieve the data.
For instance, in the
humans
table, we are able to store each member's title, first name, and
last name in separate columns because we can put them together when needed. Breaking
apart the names allows us to sort easily based on last name or first name. You'll see how
this is done in the next subsection.
Concatenating Strings
The
CONCAT()
function isvery useful for pasting together the contents of different
columns, or adding some other text to the results retrieved from a column. This is probably
the most used string function — we've already used it in several examples in previous
chapters. Within the parentheses of the function, in a comma-separated list, you give the
strings, columns, and other elements that you want to merge together into one string.
Let's look at an example of how it might be used withina
SELECT
statement. Suppose we
want to get a list of a few members and birds that they've seen. We could enter an SQL
statement like this:
SELECT CONCAT(formal_title, '. ', name_first, SPACE(1), name_last) AS
Birder,
CONCAT(common_name, ' - ', birds.scientific_name) AS Bird,
time_seen AS 'When Spotted'
FROM birdwatchers.bird_sightings
JOIN birdwatchers.humans USING(human_id)
JOIN rookery.birds USING(bird_id)
GROUP BY human_id DESC
LIMIT 4;
+----------------------+----------------------------------+--------------------+
| Birder | Bird | When
Spotted |
+----------------------+----------------------------------+--------------------+
| Ms. Marie Dyer | Red-billed Curassow - Crax blu...|
2013-10-02 07:39:44|
| Ms. Anahit Vanetsyan | Bar-tailed Godwit - Limosa lap...|
2013-10-01 05:40:00|
| Ms. Katerina Smirnova| Eurasian Curlew - Numenius arq...|
2013-10-01 07:06:46|
| Ms. Elena Bokova | Eskimo Curlew - Numenius borea...|
