Database Reference
In-Depth Information
2013-10-01 05:09:27|
+----------------------+----------------------------------+--------------------+
The first field displayed by this SQL statement is not a single column from the table, but a
CONCAT()
function that merges the bird-watcher's title, first name, and last name. We
added a period in quotes after the title, as we've decided to store the titles without a peri-
od. We used quote marks to add spaces where needed. For the second field, we concaten-
ated the common name of each bird species with the scientific name, and put spaces and a
hyphen between them.
Without
CONCAT()
, we might be tempted to combine text in one column that really
should be separated. For instance, we might put the common and scientific names of bird
species in one column. Keeping values in separate columns makes a database more effi-
cient and flexible. String functions like
CONCAT()
alleviate the need to do otherwise.
A less common concatenating functionis
CONCAT_WS()
. It puts together columns with
a separator between each. The first argument is the element you want to use as a separator
(e.g., a space) and the rest of the arguments are the values to be separated. This can be
useful when making data available for other programs.
For instance, suppose we have embroidered patches made with the name of the Rookery
site on them and we want to mail one to each premium member. To do this, we use an ad-
vertising and marketing agency that will handle the mailing. The agency needs the names
and addresses of members, and would like that data in a text file, with the values of each
field separated by vertical bars. To do this, we'll run
mysql
on the command line, passing
a single statement to it:
mysql
-
p
--
skip
-
column
-
names
-
e
\
"SELECT CONCAT_WS('|', formal_title, name_first, name_last,
street_address, city, state_province, postal_code, country_id)
FROM birdwatchers.humans WHERE membership_type = 'premium'
AND membership_expiration > CURDATE();"
>
rookery_patch_mailinglist
.
txt
This example uses
mysql
with several options. The
--skip-column-names
option
tells MySQL not to display the column headings — we want just the data separated by
bars. The
-e
option says that what follows within quotes is to be executed. We then put
the SQL statement within double quotes. The first argument to
CONCAT_WS()
is the ver-
tical bar that the company wants as a separator. The remaining arguments are the columns
to be strung together. After the closing double quotes, we use
>
to redirect the results to a
text file that we'll email to the agency. There is a potential problem with the SQL state-
ment we used. If a column has a NULL value, nothing will be exported and no bar will be
