SQL POSITION

What is the SQL POSITION function?

Returns the 1-based index of the first occurrence of a substring within a string.

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 POSITION: PostgreSQL, MySQL, MariaDB, Snowflake, Redshift support POSITION natively. SQL Server uses CHARINDEX. Oracle and SQLite use INSTR. BigQuery supports POSITION. Not available in MS Access.

SQL POSITION Full Explanation

POSITION is a standard SQL string-search function. It scans a character expression (the source string) from left to right and returns the integer position at which a specified substring first appears. If the substring is not found, it returns 0 in most databases (or NULL in some). The count starts at 1, not 0. The operation is case-sensitive in case-sensitive collations. Collation or locale settings may influence the comparison rules. Some systems expose POSITION only for compatibility and prefer dialect-specific synonyms such as STRPOS (PostgreSQL), LOCATE (MySQL), CHARINDEX (SQL Server), or INSTR (Oracle, SQLite). POSITION does not modify data, can be used in SELECT, WHERE, ORDER BY, or JOIN clauses, and works on CHAR, VARCHAR, TEXT, and compatible character types. It is deterministic and free of side effects, making it safe inside indexed expressions or deterministic functions. The function typically requires two arguments, but certain dialects offer an optional starting position to begin the search.

SQL POSITION Syntax

-- Standard SQL
POSITION(<substring> IN <string>);

-- PostgreSQL synonym
STRPOS(<string>, <substring>);

-- MySQL synonym
LOCATE(<substring>, <string>[, <start_index>]);

SQL POSITION Parameters

substring (STRING) - The sequence of characters to locate.
string (STRING) - The character expression to search within.
start_index (INTEGER, optional, MySQL only) - 1-based position at which to start searching.

Example Queries Using SQL POSITION

-- Basic usage in Standard SQL
SELECT POSITION('cat' IN 'concatenate') AS pos;  -- returns 4

-- Using in a WHERE clause (PostgreSQL)
SELECT *
FROM logs
WHERE POSITION('ERROR' IN message) > 0;

-- MySQL with start position
SELECT LOCATE('ana', 'bananarama', 3) AS pos;  -- returns 5

-- Handling no match (returns 0)
SELECT POSITION('xyz' IN 'abc') AS pos;

Expected Output Using SQL POSITION

Each query returns an integer indicating the first matching position or 0 when the substring is absent
Rows are filtered accordingly in WHERE clauses

Use Cases with SQL POSITION

Filter rows that contain a specific token
Validate that an email address contains the @ symbol
Extract or substring text after finding a delimiter
Implement simple text searches without full-text indexes
Order results by where a substring appears

Common Mistakes with SQL POSITION

Assuming the first position is 0 instead of 1
Expecting POSITION to ignore case without adjusting collation or using LOWER/UPPER
Using POSITION in SQL Server or Oracle without switching to CHARINDEX or INSTR
Forgetting that a non-match returns 0 (or NULL) and misinterpreting the result in boolean logic

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the default starting index?

The search begins at position 1 by default. Some dialects let you supply a start_index argument.

Does POSITION work with Unicode characters?

Yes. Most modern databases store text in UTF-8 or UTF-16, and POSITION operates on character positions, not bytes.

How do I perform a case-insensitive POSITION search?

Wrap both operands in LOWER or set a case-insensitive collation: `POSITION(LOWER('abc') IN LOWER(colname))`.

What happens if either argument is NULL?

POSITION returns NULL because any operation involving NULL results in NULL.