STRPOS

Return the 1-based position of the first occurrence of a substring, or 0 if not found.

Category: stringReturns: INTEGERDialect: PostgreSql

Syntax

STRPOS(str, substr)

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. STRPOS is the common SQL name for the same operation as POSITION and INSTR. The three functions are interchangeable in DeltaForge. ## 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. - Reports only the first match. ## Compatibility - Matches the common SQL STRPOS semantics. - Equivalent to INSTR(str, substr) and POSITION(substr IN str).

Parameters

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

Examples

-- Find a word
SELECT STRPOS('hello world', 'world');  -- 7

-- Not found returns 0
SELECT STRPOS('hello', 'xyz');  -- 0

-- First occurrence only
SELECT STRPOS('banana', 'an');  -- 2

-- Case-sensitive
SELECT STRPOS('Hello', 'hello');  -- 0

-- Empty substring is at position 1
SELECT STRPOS('hello', '');  -- 1

-- NULL propagates
SELECT STRPOS(CAST(NULL AS VARCHAR), 'x');  -- NULL

Pitfalls

STRPOS returns 0 for 'not found', not NULL. Test with > 0.
Positions are 1-based. Off-by-one errors are the most common bug when feeding the result into SUBSTRING or LEFT.
STRPOS counts characters, not bytes. The result is not a UTF-8 byte offset.
An empty substring returns 1. Guard with LENGTH(substr) > 0 if empty searches should be rejected.