MySQL in a Nutshell, 2nd Edition

Name

CONCAT_WS()

Synopsis

CONCAT_WS(separator, string, ...)

This function combines strings of text and columns, separated by the string specified in the first argument. Any number of strings may be specified after the first argument, with each argument separated by a comma. Null values are ignored. Here is an example:

SELECT CONCAT_WS('|', student_id, name_last, name_first)
AS 'Dyer Students'
FROM students
WHERE name_last='Dyer';

+------------------------+
| Dyer Students          |
+------------------------+
| 433342000|Dyer|Russell |
| 434892001|Dyer|Marie   |
+------------------------+

Here, the vertical bar is used to separate the columns. This function can be useful for exporting data to formats acceptable to other software. You could incorporate something like this into an API program, or just execute it from the command line using the mysql client like this:

mysql -u root -p \
-e "SELECT CONCAT_WS('|', student_id, name_last, name_first)
AS '# Dyer Students #' FROM testing.students
WHERE name_last='Dyer';" > dyer_students.txt

cat dyer_students.txt

# Dyer Students #
433342000|Dyer|Russell
434892001|Dyer|Marie

The -e option in the mysql client instructs it to execute what is contained in quotes. The entire mysql statement is followed by a > sign to redirect output to a text file. Afterward, the cat command shows the contents of that file. Notice that the usual ASCII table format is not included. This makes the file easy to import into other applications.