Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

Specifying Arbitrary Output Column Delimiters

Problem

You want mysql to produce query output using a delimiter other than tab.

Solution

Postprocess mysql’s output.

Discussion

In non-interactive mode, mysql separates output columns with tabs and there is no option for specifying the output delimiter. Under some circumstances, it may be desirable to produce output that uses a different delimiter. Suppose you want to create an output file for use by a program that expects values to be separated by colon characters (:) rather than tabs. Under Unix, you can convert tabs to arbitrary delimiters by using utilities such as tr and sed. For example, to change tabs to colons, any of the following commands would work (TAB indicates where you type a tab character):[8]

% mysql cookbook < inputfile  | sed -e "s/TAB/:/g" > outputfile
% mysql cookbook < inputfile  | tr "TAB" ":" > outputfile
% mysql cookbook < inputfile  | tr "\011" ":" > outputfile

sed is more powerful than tr because it understands regular expressions and allows multiple substitutions. This is useful when you want to produce output in something like comma-separated values (CSV) format, which requires three substitutions:

  • Escape any quote characters that appear in the data by doubling them so that when you use the resulting CSV file, they won’t be taken as column delimiters.

  • Change the tabs to commas.

  • Surround column values with quotes.

sed allows all three subsitutions to be performed in a single command:

% mysql cookbook < inputfile ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata