Functions/Text & patterns/strpos
Text & patterns Scalar

strpos()

Returns location of first occurrence of needle in haystack, counting from 1. Returns 0 if no match found

Alias of instr()

Arguments & returns

strpos(haystack, needle)

Scalar
Overload link haystackneedle Returns
#01 VARCHARVARCHAR BIGINT

Example

strpos example
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

Catalog examples

SELECT
  instr('test test', 'es');

In other engines

Apache Hivelocate()

LOCATE('a', x)
In DuckDB
STRPOS(x, 'a')

SQLGlot example ↗

BigQueryinstr()

SELECT INSTR('[email protected]', '@')
In DuckDB
SELECT STRPOS('[email protected]', '@')

SQLGlot example ↗

Used in larger rewrites 1

These examples use strpos as one part of a larger SQL translation.

Snowflakecharindex()

SELECT CHARINDEX('sub', 'testsubstring', p)
In DuckDB
SELECT CASE WHEN STRPOS(SUBSTRING('testsubstring', CASE WHEN p <= 0 THEN 1 ELSE p END), 'sub') = 0 THEN 0 ELSE STRPOS(SUBSTRING('testsubstring', CASE WHEN p <= 0 THEN 1 ELSE p END), 'sub') + CASE WHEN p <= 0 THEN 1 ELSE p END - 1 END

SQLGlot example ↗