ENDS_WITH

Return true when the input string ends with the given suffix, false otherwise.

Category: stringReturns: BOOLEANDialect: PostgreSql

Syntax

ENDS_WITH(str, suffix)

Description

## Overview Returns true when the input string ends with the given suffix, and false otherwise. ENDS_WITH (underscore form) is the common SQL name; ENDSWITH (no underscore) is an equivalent alias. The match is literal and case-sensitive. Suffix tests are a good fit for filtering by file extension, email domain, or trailing tag codes. Unlike prefix tests, suffix tests do not benefit from a plain B-tree index, so they usually trigger a full scan or require a functional index on REVERSE(col). ## Behavior - Returns NULL when either argument is NULL. - Returns true for an empty suffix: the empty string is a suffix of every string. - Returns false when the suffix 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. ## Compatibility - Semantically equivalent to str LIKE '%' || suffix after wildcard escaping. - Semantically equivalent to RIGHT(str, LENGTH(suffix)) = suffix for non-NULL inputs.

Parameters

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

Examples

-- Matching suffix
SELECT ENDS_WITH('hello world', 'world');  -- true

-- Non-matching suffix
SELECT ENDS_WITH('hello world', 'hello');  -- false

-- Case-sensitive
SELECT ENDS_WITH('Hello', 'hello');  -- false

-- Empty suffix always matches
SELECT ENDS_WITH('hello', '');  -- true

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

-- Filter files by extension
SELECT file_name
FROM ops.etl.staging_files
WHERE ENDS_WITH(file_name, '.parquet');

Pitfalls

ENDS_WITH is case-sensitive. LOWER both sides for case-insensitive matching.
ENDS_WITH is not a pattern matcher. '.%' is treated as two literal characters, not a LIKE pattern.
ENDS_WITH(col, literal) cannot use a plain B-tree index on col. To accelerate suffix filters on a hot column, create a functional index on REVERSE(col) and rewrite the predicate as STARTS_WITH(REVERSE(col), REVERSE(literal)).
An empty suffix returns true for every non-NULL row. Guard with LENGTH(suffix) > 0 if needed.