POSITION

Return the 1-based position of the first occurrence of a substring (SQL-standard IN syntax).

Category: stringReturns: INTEGERDialect: Standard

Syntax

POSITION(substr IN str)

Description

## Overview Returns the 1-based character position of the first occurrence of the substring within the input string, or 0 if the substring is not found. POSITION is the SQL-standard spelling and uses the keyword-style syntax POSITION(substr IN str). It is semantically identical to INSTR(str, substr) and STRPOS(str, substr). POSITION is often preferred in SQL-standard-conformant code for its readable keyword form; INSTR and STRPOS use a more familiar function-call form for the same operation. ## Behavior - Returns NULL when either argument is NULL. - Returns 0 when the substring is not found. - Returns 1 when the substring is an empty string. - Positions are 1-based and count Unicode code points, not bytes. - The search is case-sensitive and literal; no pattern interpretation is performed. - Reports only the first match. ## Compatibility - Fully conforms to the SQL standard POSITION(substr IN str) syntax. - Equivalent to INSTR and STRPOS.

Parameters

Name	Type	Description
`substr`		Specifies the substring to locate. The search is case-sensitive and literal.
`str`		Specifies the string to search within.

Examples

-- Find a word
SELECT POSITION('world' IN 'hello world');  -- 7

-- Not found returns 0
SELECT POSITION('xyz' IN 'hello');  -- 0

-- First occurrence only
SELECT POSITION('l' IN 'hello');  -- 3

-- Empty substring is at position 1
SELECT POSITION('' IN 'hello');  -- 1

-- NULL propagates
SELECT POSITION('x' IN CAST(NULL AS VARCHAR));  -- NULL

-- Filter rows whose notes contain a keyword (use as a boolean)
SELECT order_id
FROM retail.sales.orders
WHERE POSITION('refund' IN LOWER(notes)) > 0;

Pitfalls

Note the argument order: substring first, then IN, then the string. Swapping them silently returns the wrong answer because both are valid strings.
POSITION returns 0 for 'not found', not NULL. Test with > 0, not IS NOT NULL.
Positions are 1-based. Passing the result as a 0-based offset into a slicing function is off by one.
An empty substring returns 1. Guard with LENGTH(substr) > 0 if empty searches should be rejected.