Database Reference
In-Depth Information
put in the file to indicate an empty field. Here's an example of how the text file would
look:
Ms|Rusty|Osborne|ch
Ms|Elena|Bokova|ru
We have only four fields for these members, although we told MySQL to export eight
fields. If these two records were in the midst of thousands of records, they would cause er-
rors that might not be obvious when imported. Although it's more cumbersome, we
should wrap each column name in an
IFNULL()
function. Then we can give a value to
display if the column is NULL, such as the word
unknown
or a blank space. Here's the
same example again, but withthe
IFNULL()
function:
mysql
-
p
--
skip
-
column
-
names
-
e
\
"SELECT CONCAT_WS('|', IFNULL(formal_title, ' '), IFNULL(name_first,
' '),
IFNULL(name_last, ' '), IFNULL(street_address, ' '),
IFNULL(city, ' '), IFNULL(state_province, ' '),
IFNULL(postal_code, ' '), IFNULL(country_id, ' '))
FROM birdwatchers.humans WHERE membership_type = 'premium'
AND membership_expiration > CURDATE();"
>
rookery_patch_mailinglist
.
txt
It looks daunting and excessive, but it's simple to MySQL. The new contents of the text
file follow:
Ms|Rusty|Osborne| | | | |ch
Ms|Elena|Bokova| | | | |ru
That's a manageable data file. When the results are like this, the marketing company can
import all of the records without errors and then contact us to try to get the missing in-
formation. They can add it to their system without having to reimport the textfile.
Setting Case and Quotes
Occasionally, youmight want to convertthe text from a column to either all lowercase let-
ters or all uppercase letters. For these situations, thereare
LOWER()
and
UPPER()
,
which can also bespelled
LCASE()
and
UCASE()
, respectively. In the example that fol-
lows, the output of the first column is converted to lowercase and the second to uppercase:
SELECT LCASE(common_name) AS Species,
UCASE(bird_families.scientific_name) AS Family
FROM birds
JOIN bird_families USING(family_id)
WHERE common_name LIKE '%Wren%'
ORDER BY Species
