SQL TERMINATED

What does the SQL TERMINATED BY clause do?

TERMINATED BY specifies the character sequence that separates fields or lines when bulk loading or unloading data.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL TERMINATED:

SQL TERMINATED Full Explanation

The TERMINATED BY clause appears inside bulk-data commands such as LOAD DATA INFILE (MySQL), COPY (PostgreSQL), and CREATE TABLE ... ROW FORMAT DELIMITED (Hive). It tells the database which byte or string marks the end of each field or line in a text file. By overriding the default delimiter (often a tab or comma) you can import or export pipe-delimited, semicolon-delimited, or other custom formats without post-processing.There are usually two flavors:1. FIELDS TERMINATED BY - delimiter between individual column values2. LINES TERMINATED BY - delimiter between recordsMany dialects let you combine TERMINATED BY with ENCLOSED BY or ESCAPED BY for more precise parsing. The clause is not part of the ANSI SQL standard, so exact syntax and supported escape sequences vary by engine. Always consult the dialect documentation when moving scripts between systems.

SQL TERMINATED Syntax

-- MySQL LOAD DATA
LOAD DATA INFILE '/path/file.psv'
INTO TABLE sales
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
LINES  TERMINATED BY '\n';

-- PostgreSQL COPY
COPY sales FROM '/path/file.psv'
WITH (FORMAT csv, DELIMITER '|');

-- Hive external table definition
CREATE TABLE sales(
  id          INT,
  total_price DECIMAL(10,2)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\u0001'
LINES TERMINATED BY '\n';

SQL TERMINATED Parameters

delimiter_string (string) - One or more characters designating the boundary between fields or lines.
scope (enum) - Either FIELDS or LINES, indicating whether the delimiter separates columns or records.

Example Queries Using SQL TERMINATED

-- Import pipe-separated values into MySQL
LOAD DATA INFILE '/imports/inventory.psv'
INTO TABLE inventory
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n';

-- Export tab-separated data from PostgreSQL
COPY (SELECT * FROM inventory)
TO '/exports/inventory.tsv'
WITH (FORMAT csv, DELIMITER E'\t');

Expected Output Using SQL TERMINATED

Data is parsed using the specified delimiter so each row and column lands in the correct table cell
No result set is returned, only a count of affected rows

Use Cases with SQL TERMINATED

Loading pipe-delimited or semicolon-delimited files received from partners
Exporting query results with a specific delimiter required by downstream systems
Defining external tables on Hadoop where the default tab delimiter conflicts with actual data
Handling CSV files that use Windows line endings (\r\n)

Common Mistakes with SQL TERMINATED

Forgetting to quote special characters like tab (\t) or newline (\n)
Mixing up FIELDS TERMINATED BY with LINES TERMINATED BY
Using multi-character delimiters in engines that only accept a single byte
Assuming all databases support TERMINATED BY when some require DELIMITER or FORMAT options

First Introduced In

MySQL 3.23 (1999)

Frequently Asked Questions

What characters can be used as delimiters?

Any printable character or escape sequence that does not appear inside your data values. Common choices include comma (,), pipe (|), tab (\t), and semicolon (;).

Does TERMINATED BY work in SQL Server?

No. SQL Server uses BULK INSERT with the FIELDTERMINATOR and ROWTERMINATOR options instead.

How do I load a CSV with Windows line endings?

Specify LINES TERMINATED BY '\r\n' in MySQL or set DELIMITER to E'\r\n' in PostgreSQL COPY.

What happens if the delimiter also appears inside a field?

The import will misalign columns unless the value is quoted and the command includes an ENCLOSED BY option that tells the engine how to treat quoted strings.