SQL STARTING

What is the SQL STARTING keyword?

STARTING is a reserved word used in clauses such as STARTING WITH and LINES STARTING BY to indicate that a comparison or file-load operation should begin at the first character of a value.

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 STARTING:

SQL STARTING Full Explanation

STARTING is not a stand-alone command; it appears inside larger statements in several SQL dialects. The two most common contexts are:1. Prefix comparison (Firebird and InterBase) SELECT … WHERE STARTING WITH performs a case-sensitive, index-friendly prefix search. It behaves like LIKE '%' but without wildcard processing after the prefix, making the operation faster and predictable. Collation rules apply, and an index on the searched column can be used if the literal does not begin with a wildcard.2. File import (MySQL and MariaDB) In LOAD DATA [LOCAL] INFILE, the LINES STARTING BY 'prefix' clause tells the server to treat only the part of each line after the specified prefix as actual data. Lines missing the prefix are skipped. This is useful when the source file embeds markers or comments at the start of each record.Because STARTING is embedded in larger statements, it inherits their transactional behavior and error handling. It is reserved in dialects that implement one of the above features, so avoid using it as an unquoted identifier.

SQL STARTING Syntax

-- Firebird / InterBase
SELECT *
FROM customers
WHERE name STARTING WITH 'Al';

-- MySQL / MariaDB
LOAD DATA INFILE '/tmp/sales.txt'
INTO TABLE sales
FIELDS TERMINATED BY ','
LINES STARTING BY '#'
(id, amount, created_at);

SQL STARTING Parameters

Prefix string (literal or parameter) - The characters that must appear at the start of the compared column or source line.

Example Queries Using SQL STARTING

-- 1. Find all customers whose names start with "Al"
SELECT id, name
FROM customers
WHERE name STARTING WITH 'Al';

-- 2. Load a text file where every record begins with "##"
LOAD DATA INFILE '/tmp/events.log'
INTO TABLE events
FIELDS TERMINATED BY '\t'
LINES STARTING BY '##'
(event_time, user_id, action);

Expected Output Using SQL STARTING

Query 1 returns only rows where the name column begins with Al, for example "Alice" and "Alfred"
Query 2 imports each line after the prefix ## into the target table; the prefix itself is discarded

Use Cases with SQL STARTING

Fast prefix lookups without wildcards
Importing log files or data feeds that use a fixed line prefix
Cleaning comment markers while bulk-loading data

Common Mistakes with SQL STARTING

Expecting STARTING WITH to honor trailing wildcards like LIKE does – it does not.
Forgetting that the comparison is case-sensitive unless a case-insensitive collation is applied.
Using STARTING as an unquoted column or table name in dialects where it is reserved.
Omitting the surrounding LOAD DATA clause when using LINES STARTING BY in MySQL.

First Introduced In

InterBase 6.0 (STARTING WITH), MySQL 4.1 (LINES STARTING BY)

Frequently Asked Questions

What does STARTING WITH do?

STARTING WITH compares a column to a literal and returns rows where the column begins with that literal. It is faster than LIKE because it avoids wildcard evaluation after the prefix.

How do I ignore line prefixes while importing data in MySQL?

Use LINES STARTING BY 'prefix' inside LOAD DATA INFILE. MySQL discards the prefix and parses the remaining part of each line.

Can I use indexes with STARTING WITH?

Yes. If the literal is constant and does not start with a wildcard, the database can use an index on the searched column for rapid lookup.

Is STARTING reserved in every database?

No. It is reserved only in dialects that implement STARTING WITH or LINES STARTING BY. In others, it can be used as an identifier, but quoting is recommended for portability.