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

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 the SQL query.

But, if you are new to the SQL world, it's better to start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy. That will help you to learn SQL better and quicker, and these kinds of articles will also make more sense once you have some SQL knowledge under your belt.


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 the above SQL commands work as expected:

That's all about how to remove leading and trailing space from a String in Microsoft SQL Server. This SQL query is tested in Microsoft SQL Server 2014 edition and it should work fine in most of the SQL versions. If you face any issue running on any SQL Server, please drop a comment.

Other SQL Server tutorials

If you like this short and sweet tutorial and looking for some more SQL Server gems then check out my following post,  you may like them as well:

• 5 Free Oracle and SQL Server courses for Programmers (courses)
• How to replace NULL with empty String in SQL Server? (tutorial)
• How to find the length of a string in SQL Server? (solution)
• How to split String in SQL Server database? (answer)
• 50 SQL Server Phone Interview Question (SQL server questions)
• The difference between char, varchar, nchar, and nvarchar in SQL Server? (answer)
• How to join more than two tables in one SQL query? (solution)
• How to create an Identity column in Microsoft SQL Server? (example)
• 5 tips while migrating from Oracle to SQL SERVER? (tips)
• How to find the second highest salary of an employee in SQL Server? (query)
• What is the difference between WHERE and HAVING clause in SQL Server? (answer)
• How to find duplicate records from a table? (solution)
• 5 Websites to learn SQL online for FREE? (resource)
• Top 5 Courses to learn Database and SQL Online (courses)

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

P. S. - If you are new to SQL and Microsoft SQL Server and want to learn SQL and database concepts from start and looking for free online courses then you can also check out these free SQL and database courses to start your SQL journey. They are the best free courses to learn SQL from Udemy and Coursera.