SQL Server TRIM() Function

 The TRIM() function in SQL Server is used to remove leading and trailing spaces (or other specified characters) from a string.

Syntax

TRIM([characters FROM] string)

• characters: (Optional) The characters to remove. If omitted, spaces are removed by default.
• string: The input string from which to trim the characters.

Key Points

1. If you don't specify characters, TRIM() removes spaces.
2. You can explicitly define characters to remove using the characters FROM syntax.

Example Usage

1. Trim Spaces (Default Behavior)

SELECT TRIM('   Hello World   ') AS TrimmedString;
-- Result: 'Hello World'

2. Trim Specific Characters

SELECT TRIM('x' FROM 'xxxHello Worldxxx') AS TrimmedString;
-- Result: 'Hello World'

3. Using TRIM() with Table Columns

CREATE TABLE SampleTable (
    ID INT,
    Name NVARCHAR(50)
);

INSERT INTO SampleTable (ID, Name)
VALUES (1, '   John Doe   '), 
       (2, '   Jane Smith   ');

SELECT ID, TRIM(Name) AS TrimmedName
FROM SampleTable;
-- Removes leading and trailing spaces from the 'Name' column

Notes

• The TRIM() function was introduced in SQL Server 2017 (version 14.x). For earlier versions, you can achieve similar functionality using a combination of LTRIM() and RTRIM():

  SELECT LTRIM(RTRIM('   Hello World   ')) AS TrimmedString;
  

This makes TRIM() a straightforward and modern way to clean up strings in SQL Server.