STARTSWITH

Return true when the input string begins with the given prefix, false otherwise.

Category: stringReturns: BOOLEANDialect: Standard

Syntax

STARTSWITH(str, prefix)

Description

## Overview Returns true when the input string begins with the given prefix, and false otherwise. STARTSWITH (no underscore) is the standard-dialect spelling; STARTS_WITH is a common SQL alias with identical semantics. The match is literal and case-sensitive. Prefix tests are the simplest way to partition data by a known-length leading code, dispatch rows into category buckets, and filter URLs, file paths, or namespaces. ## Behavior - Returns NULL when either argument is NULL. - Returns true for an empty prefix. - Returns false when the prefix is longer than the input string. - The match is case-sensitive and literal; SQL wildcards and regex metacharacters are not interpreted. - Operates on Unicode code points; multi-byte UTF-8 characters are matched correctly. - Short-circuits on the first mismatched character. ## Compatibility - STARTSWITH and STARTS_WITH are both supported and behave identically. - Equivalent to str LIKE prefix || '%' after wildcard escaping.

Parameters

Name	Type	Description
`str`		Specifies the input string to test.
`prefix`		Specifies the prefix to check for. The match is case-sensitive and literal.

Examples

-- Check URL protocol
SELECT STARTSWITH('https://example.com', 'https://');  -- true

-- Non-matching prefix
SELECT STARTSWITH('hello', 'world');  -- false

-- Case-sensitive
SELECT STARTSWITH('ABC', 'abc');  -- false

-- Empty prefix matches
SELECT STARTSWITH('anything', '');  -- true

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

-- Filter file paths by directory
SELECT file_path
FROM ops.etl.staging_files
WHERE STARTSWITH(file_path, '/var/log/');

Pitfalls

STARTSWITH is case-sensitive. LOWER both sides for case-insensitive matching.
STARTSWITH does not interpret wildcards. 'a%' is three literal characters, not a LIKE pattern.
For very hot prefix filters, consider a trigram or GIN index. A plain B-tree index on the column can still support STARTSWITH(col, literal) as a range scan.
An empty prefix matches every non-NULL row. Guard against accidentally passing an empty literal.