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.
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.