SQL TRIM Function

The SQL TRIM is a String Function, which is used to remove empty spaces or special characters from both left side and Right side of a string expression. In this article we will show you, How to write / use TRIM in SQL Server with example.

SQL TRIM Function Syntax

The basic syntax of the SQL Server TRIM Function is as shown below:

SELECT TRIM (Character_Expression)
FROM [Source]

SELECT TRIM ('Special_Characters' FROM Character_Expression)
FROM [Source]

Character_Expression: Please specify a valid Expression on which you want to remove empty spaces or special char caters from left and right side.

For this demonstration, we are going to use below shown data

SQL TRIM Function Example 1

SQL String TRIM Function removes space or any special character from the left and right side of a string. The following query will show multiple ways to use TRIM function.

-- SQL Server TRIM Function
DECLARE @str VARCHAR(50)
SET @str = '            Tutorial Gateway'

SELECT TRIM(@str) AS Result1;

SELECT TRIM('     Welcome to SQL           ') AS Result2;  

SELECT TRIM('      We Provide SQL Server Tutorials at Tutorial Gateway        ') AS Result3;

OUTPUT

SQL TRIM Function Example 2

In this example, we are using Trim function to remove special characters from a string.

-- SQL Server TRIM Function
DECLARE @str VARCHAR(50)
SET @str = '* Tutorial Gateway'

SELECT TRIM('*' FROM @str) AS Result1;

SELECT TRIM('# *' FROM '####     Welcome to SQL    ** ') AS Result2;  

SELECT TRIM('@ #' FROM ' @@@###$$$     We Provide SQL Tutorials at Tutorial Gateway  [email protected]@##      ') AS Result3;

OUTPUT

SQL TRIM Function Example 3

This example will show you, How we are going to use the SQL String TRIM function on Column Names. In general, we may get data with some extra spaces due to typing mistake, or any other data entry issues. If this is the case then you can this TRIM, or RTRIM or LTRIM to trim those extra spaces.

-- SQL Server TRIM Function
USE [SQL Tutorial]
GO
SELECT TRIM([FirstName]) + ' ' + TRIM([LastName]) AS FullName
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
	  ,Sales
	  ,HireDate
 FROM [Employee]

OUTPUT