Supported Editors

Free Tools

Resources

Blog

Docs

Pricing

Book a Demo

B

Get Free Extension

S

Get Sherloq Free

Back to Library

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"

Notes:

  • Behavior: By default, removes spaces from both ends

  • Performance Considerations: Efficient for basic string cleaning

  • Version Info: Core SQL function, available in most DBMS versions

Error Handling:

  • Error: Returns NULL for NULL input

Supported Databases:

DBMS
Function / Syntax
Example
Behavior with NULL
MySQL

TRIM()

TRIM(' abc ')

Returns NULL

PostgreSQL

TRIM()

TRIM(BOTH FROM ' abc ')

Returns NULL

SQL Server

TRIM()

TRIM(' abc ')

Returns NULL

SQLite

TRIM()

TRIM(' abc ')

Returns NULL

BigQuery

TRIM()

TRIM(' abc ')

Returns NULL

Snowflake

TRIM()

TRIM(' abc ')

Returns NULL

Athena

TRIM()

TRIM(' abc ')

Returns NULL

Related Functions:

REPLACE()

Replaces all occurrences of a substring within a string with another substring.

REVERSE()

Returns a string value in a reversed order (characters displayed in reverse order).

Returns a string value in a reversed order (characters displayed in reverse order).

Returns a string value in a reversed order (characters displayed in reverse order).

Returns a string value in a reversed order (characters displayed in reverse order).

STUFF()

Deletes a specified length of characters and inserts another string at a specified start position in a string.

Deletes a specified length of characters and inserts another string at a specified start position in a string.

Deletes a specified length of characters and inserts another string at a specified start position in a string.

Deletes a specified length of characters and inserts another string at a specified start position in a string.

PATINDEX()

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

RIGHT()

Extracts a specified number of characters from the end (right side) of a string.

Extracts a specified number of characters from the end (right side) of a string.

Extracts a specified number of characters from the end (right side) of a string.

Extracts a specified number of characters from the end (right side) of a string.

CHARINDEX()

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

LEFT()

Extracts a specified number of characters from the beginning (left side) of a string.

Extracts a specified number of characters from the beginning (left side) of a string.

Extracts a specified number of characters from the beginning (left side) of a string.

Extracts a specified number of characters from the beginning (left side) of a string.

LENGTH()/LEN()

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

TRIM()

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

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

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

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

LOWER()

Converts all characters in a string to lowercase letters.

Converts all characters in a string to lowercase letters.

Converts all characters in a string to lowercase letters.

Converts all characters in a string to lowercase letters.

SUBSTRING()

Extracts a portion of a string based on specified position and length.

Extracts a portion of a string based on specified position and length.

Extracts a portion of a string based on specified position and length.

Extracts a portion of a string based on specified position and length.

UPPER()

Converts all characters in a string to uppercase letters.

Converts all characters in a string to uppercase letters.

Converts all characters in a string to uppercase letters.

Converts all characters in a string to uppercase letters.

CONCAT()

Concatenates two or more strings into a single string.

Concatenates two or more strings into a single string.

Concatenates two or more strings into a single string.

Concatenates two or more strings into a single string.

One place for all your queries,
directly on your SQL editor

Get Free Extension

Book a Demo

B

Get Sherloq Free

S

Explore

Features

Supported Editors

AppsFlyer Case Study

Weski Case Study

Resources

Pricing

Blog

Docs

Partners

Community

Referral Program

Free Tools

AI SQL Organizer

SQL Visual Tool (ERD)

SQL Greeting Generator

Legal

Privacy policy

Terms & Conditions

Security

Book a Demo

B

Get Sherloq Free

S

Explore

Features

Supported Editors

AppsFlyer Case Study

Weski Case Study

Resources

Pricing

Blog

Docs

Partners

Community

Referral Program

Free Tools

AI SQL Organizer

SQL Visual Tool (ERD)

SQL Greeting Generator

Legal

Privacy policy

Terms & Conditions

Security

Book a Demo

B

Get Sherloq Free

S

Explore

Features

Supported Editors

AppsFlyer Case Study

Weski Case Study

Resources

Pricing

Blog

Docs

Partners

Community

Referral Program

Free Tools

AI SQL Organizer

SQL Visual Tool (ERD)

SQL Greeting Generator

Legal

Privacy policy

Terms & Conditions

Security