BTRIM

Strip a custom character set from both ends of the input string, defaulting to whitespace.

Category: stringReturns: VARCHARDialect: PostgreSql

Syntax

BTRIM(str [, chars])

Description

## Overview Removes the longest run of characters at both the start and the end of the input string that appears in the supplied character set, and returns the remainder. When the character set is omitted, BTRIM behaves like TRIM and strips whitespace. BTRIM is the right tool when you need to peel off wrapping characters such as quotes, brackets, currency symbols, or padding glyphs. The character-set argument is treated as an unordered set, not a substring. BTRIM('xyhello', 'xy') strips any leading or trailing occurrence of 'x' or 'y' regardless of order, producing 'hello'. ## Behavior - Returns NULL when either argument is NULL. - Returns an empty string when the input is empty or consists entirely of characters in the set. - Interior characters are never touched, only the prefix and suffix runs that consist solely of characters in the set. - Character matching is case-sensitive. BTRIM('XxyYz', 'x') removes only the first character. - Operates on Unicode code points, so multi-character code points in 'chars' are accepted and multi-byte input characters are respected. - Equivalent to applying LTRIM(chars) then RTRIM(chars), or the SQL-standard TRIM(BOTH chars FROM str). ## Compatibility - Matches the common SQL BTRIM semantics. - The single-argument form behaves identically to TRIM(str).

Parameters

Name	Type	Description
`str`		Specifies the input string to trim from both ends.
`chars`		Specifies the set of characters to remove. Each character in the string is treated individually; the argument is not a pattern. Defaults to the whitespace set when omitted.

Examples

-- Default: trim whitespace from both ends
SELECT BTRIM('  hello  ');  -- 'hello'

-- Strip a single character
SELECT BTRIM('xxhelloxx', 'x');  -- 'hello'

-- Strip any of several characters
SELECT BTRIM('xyzhellozyx', 'xyz');  -- 'hello'

-- Strip quotes around a quoted token
SELECT BTRIM('"quoted"', '"');  -- 'quoted'

-- Normalise a column that arrived wrapped in brackets
SELECT BTRIM(raw_code, '[]') AS code
FROM ops.etl.staging_orders;

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

Pitfalls

The second argument is a character set, not a substring. BTRIM('abcabc', 'abc') strips every prefix and suffix character that is any of a/b/c, producing an empty string, not the literal substring 'abc'.
Matching is case-sensitive. BTRIM('XxY', 'x') yields 'XxY' with the leading X untouched. Uppercase the set or normalise the input if you need a case-insensitive trim.
Because matching is per code point, surrogate pairs should be expressed in UTF-8 form. Multi-byte characters are fine; combining sequences (base + accent) are not matched as a single unit.
To strip an exact substring (for example a prefix like 'Mr. '), use LEFT/SUBSTRING or a regex, not BTRIM.