How to Remove Leading/Trailing White Space from a String in SQL Server? LTRIM, RTRIM Example

Javin PaulOctober 23rd, 2018Last Updated: October 23rd, 2018

0 114 1 minute read

Unlike Java, Microsoft SQL Server 2008, 2012, 2014, and even the latest version don’t have a built-in trim() function, which can remove both leading and trailing space from the given String. But, SQL Server does have two built-in functions LTRIM() and RTRIM() to remove leading and trailing space. The LTRIM() function removes space from the left side of String so you can use it to get rid of leading space, while RTRIM() removes white-space from the right side of String so you can use it to delete trailing space. You can even combine these two methods to create your own TRIM() method in SQL SERVER e.g. LTRIM(RTRIM(column)) will act as a TRIM() method because it removes both leading and trailing space.

How to use LTRIM() and RTRIM() in SQL Server

You can use LTRIM() and RTRIM function like any other built-in function. You can apply it to a value or a column in SQL query.

Here is a couple of example of LTRIM function in Microsoft SQL Server database:

// variable declaration
DECLARE @name VARCHAR(20)

// assigning value to variable 
SELECT @name = ' Microsoft ';

// other variable to store result of trim operations
DECLARE @withoutLeadingSpace VARCHAR(20)
SELECT @withoutLeadingSpace = LTRIM(@name);
DECLARE @withoutTrailingSpace VARCHAR(20)
SELECT @withoutTrailingSpace = RTRIM(@name);

// printing output
SELECT @name as name, 
       @withoutLeadingSpace as [LTRIM], 
       @withoutTrailingSpace as [RTRIM] 



Output
Microsoft 
Microsoft 
Microsoft

You can see that LTRIM() has removed leading space, while RTRIM() has removed trailing space in SQL Server. If you want to remove both leading and trailing space in one short, you can combine LTRIM, RTRIM like shown below:

SELECT LTRIM(RTRIM(name))as Name from Employee

Here is the screenshot from my Microsoft SQL Server Management Studio to confirm that above SQL commands work as expected:

Thanks for reading this SQL Server tutorial so far. If you like this article then please share with your friends and colleagues. If you have any questions or feedback then please drop a note.

Published on Java Code Geeks with permission by Javin Paul, partner at our JCG program. See the original article here: How to Remove Leading/Trailing White Space from a String in SQL Server? LTRIM, RTRIM Example

Opinions expressed by Java Code Geeks contributors are their own.