SQL BOTH

What is SQL BOTH?

BOTH is a TRIM qualifier that removes the specified characters from both the beginning and end of a string.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL BOTH: PostgreSQL, MySQL 8.0+, MariaDB 10.3+, Oracle, SQLite, DuckDB, BigQuery. SQL Server supports only plain TRIM() starting 2017, not the BOTH syntax.

SQL BOTH Full Explanation

BOTH is an optional keyword used inside the ANSI-standard TRIM function. It tells the database engine to eliminate the chosen character (or the default blank space) from both the left and right sides of the input string. When BOTH is omitted, many dialects assume BOTH by default, but writing it explicitly increases clarity and cross-dialect safety. BOTH cannot stand alone; it must appear inside a TRIM call and is always paired with FROM to separate the trim character(s) from the source string.Behavior:- If no trim character is supplied, TRIM(BOTH FROM str) removes leading and trailing spaces.- If a single character or string literal is supplied, TRIM(BOTH 'x' FROM str) removes every contiguous occurrence of that literal from both ends until a different character is encountered.- The inner content of the string is untouched.Caveats:- Some databases (notably older SQL Server versions) do not accept the ANSI syntax and instead expose TRIM(), LTRIM(), RTRIM().- Multibyte or Unicode variants trim correctly as long as the database collation treats them as single characters.- NULL input returns NULL.

SQL BOTH Syntax

TRIM(BOTH FROM <source_string>);
TRIM(BOTH <trim_character> FROM <source_string>);

SQL BOTH Parameters

Example Queries Using SQL BOTH

--Remove outer spaces
SELECT TRIM(BOTH FROM '   galaxy   ');

--Strip hyphens at both ends
SELECT TRIM(BOTH '-' FROM '---sql-both---');

--Mixed use inside update
UPDATE users
SET username = TRIM(BOTH '_' FROM username);

Expected Output Using SQL BOTH

The first query returns 'galaxy'
The second query returns 'sql-both'
The UPDATE statement persists usernames without leading or trailing underscores

Use Cases with SQL BOTH

Normalizing user input by discarding accidental leading and trailing blanks.
Cleaning CSV imports that pad values with special characters.
Updating legacy columns that store delimiters around data.
Building deterministic comparisons by ensuring outer whitespace is gone.

Common Mistakes with SQL BOTH

Writing TRIM(BOTH 'x', string) instead of TRIM(BOTH 'x' FROM string).
Using BOTH without FROM.
Assuming BOTH works as a stand-alone function.
Expecting SQL Server 2012 or earlier to accept the ANSI syntax.

First Introduced In

SQL:1999 standard

Frequently Asked Questions

What is the difference between BOTH, LEADING, and TRAILING?

BOTH removes characters on both sides, LEADING only at the beginning, and TRAILING only at the end of the string.

Can I omit FROM in TRIM(BOTH ...)?

No. The ANSI syntax requires the keyword FROM to separate the trim characters from the source string.

What character is removed if I provide none?

A single space (ASCII 32) is assumed by default when no trim character is specified.

How do I trim multiple different characters?

Use nested TRIM calls or REGEXP_REPLACE. TRIM only removes one literal or a space at a time.