TRIM() in SQL

Removes leading and/or trailing spaces (or specified characters) from a string.

Syntax

TRIM([{LEADING | TRAILING | BOTH}] [removal_string] FROM string)
-- or simple form
TRIM(string)

Return type

VARCHAR (or equivalent string type in the respective DBMS)

TRIM() Function Example

SELECT TRIM('   Hello World   ');
-- Output: 'Hello World'

What is TRIM() in SQL?

The TRIM() function in SQL removes leading and/or trailing spaces (or specified characters) from a string. This is useful for cleaning up data, ensuring consistency in string comparisons, and avoiding issues caused by accidental white spaces in stored values. The function supports both LTRIM() (removing leading spaces) and RTRIM() (removing trailing spaces) in some databases. It is widely used across SQL systems like MySQL, PostgreSQL, SQL Server, and Oracle to format text fields, clean user input, and optimize string-based queries by ensuring uniformity in stored and retrieved data.

Parameters:

string: The input string to be trimmed
removal_string: Optional characters to remove (defaults to space)
LEADING/TRAILING/BOTH: Optional specification of where to trim

Example Use Cases:

-- Clean user input data

SELECT TRIM(user_input) AS cleaned_input FROM form_submissions;
-- Output:
user_input               cleaned_input
----------------------  ----------------------
"   John Smith   "      "John Smith"
"Hello World!    "      "Hello World!"
"     Data Entry"       "Data Entry"
"  SQL Query  "         "SQL Query"
" New User    "         "New User"

-- Remove specific characters

SELECT TRIM(BOTH '/' FROM '/path/to/file/') AS clean_path;
-- Output:
input_path              clean_path
----------------------  ----------------------
'/path/to/file/'        'path/to/file'
'//documents//'         'documents'
'/images/photos/'       'images/photos'
'///root///'           'root'
'/home/user/'          'home/user'

-- Normalize contact data

SELECT TRIM(phone_number) AS formatted_phone FROM contacts;
-- Output:
phone_number            formatted_phone
----------------------  ----------------------
" (555) 123-4567 "     "(555) 123-4567"
"555-123-4567  "       "555-123-4567"
"  123.456.7890"       "123.456.7890"
" 1-555-123-4567 "     "1-555-123-4567"
"  987 654 3210  "     "987 654 3210"