Return the 1-based position of the first occurrence of a substring, or 0 if not found.
INSTR(str, substr)## 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. INSTR is functionally equivalent to STRPOS and to POSITION(substr IN str); all three return the same result for the same inputs. Use INSTR to split a string at the first occurrence of a delimiter, to test containment (INSTR(s, sub) > 0), and to index into a string by finding a landmark character. ## 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 (the empty string is considered to be at the start). - Positions are 1-based and count Unicode code points, not bytes. - The search is case-sensitive and literal. - Reports only the first match; subsequent occurrences are ignored. ## Compatibility - INSTR, STRPOS, and POSITION are interchangeable in DeltaForge. - The 2-argument form matches the common SQL convention. Some dialects provide a 4-argument INSTR(str, substr, start, occurrence); DeltaForge supports that extended form with the same 1-based semantics.
| Name | Type | Description |
|---|---|---|
str | Specifies the string to search within. | |
substr | Specifies the substring to locate. The search is case-sensitive and literal. |
-- Find a word
SELECT INSTR('hello world', 'world'); -- 7-- Not found returns 0
SELECT INSTR('hello world', 'xyz'); -- 0-- First occurrence only
SELECT INSTR('abcabc', 'bc'); -- 2-- Case-sensitive
SELECT INSTR('Hello', 'hello'); -- 0-- NULL propagates
SELECT INSTR(CAST(NULL AS VARCHAR), 'x'); -- NULL-- Position of '@' to split an email into local and domain parts
SELECT SUBSTRING(email, 1, INSTR(email, '@') - 1) AS local_part
FROM retail.customers.profiles
WHERE INSTR(email, '@') > 0;