ENDSWITH

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

Category: stringReturns: BOOLEANDialect: Standard

Syntax

ENDSWITH(str, suffix)

Description

## Overview Returns true when the input string ends with the given suffix, and false otherwise. ENDSWITH (no underscore) is the standard-dialect spelling; ENDS_WITH is a common SQL alias with identical semantics. The match is literal and case-sensitive. Suffix tests are a good fit for filtering by file extension, email domain, or trailing tag codes. ## Behavior - Returns NULL when either argument is NULL. - Returns true for an empty suffix. - Returns false when the suffix is longer than the input string. - The match is case-sensitive and literal. - Operates on Unicode code points; multi-byte UTF-8 characters are matched correctly. ## Compatibility - ENDSWITH and ENDS_WITH are both supported and behave identically. - Equivalent to str LIKE '%' || suffix after wildcard escaping.

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

-- Check file extension
SELECT ENDSWITH('report.csv', '.csv');  -- true

-- Non-matching suffix
SELECT ENDSWITH('report.csv', '.json');  -- false

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

-- Empty suffix always matches
SELECT ENDSWITH('anything', '');  -- true

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

-- Route rows by email provider
SELECT CASE WHEN ENDSWITH(LOWER(email), '@gmail.com') THEN 'gmail'
            WHEN ENDSWITH(LOWER(email), '@yahoo.com') THEN 'yahoo'
            ELSE 'other' END AS provider
FROM retail.customers.profiles;

Pitfalls

ENDSWITH is case-sensitive. LOWER both sides for case-insensitive matching.
ENDSWITH does not interpret wildcards. '.%' is two literal characters, not a LIKE pattern.
ENDSWITH(col, literal) cannot use a plain B-tree index. For hot-path suffix filters, index REVERSE(col) and query STARTSWITH(REVERSE(col), REVERSE(literal)).
An empty suffix matches every non-NULL row. Guard against passing an empty literal by accident.