Sunday, November 4, 2018

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 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:

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



Further Learning 
The Complete SQL Bootcamp 
Microsoft SQL for Beginners 
SQL Server Fundamentals by Dan Sullivan
Microsoft SQL Server 2012 T-SQL Fundamentals,


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 2008? (answer)
  • 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 with your friends and colleagues. If you have any questions or feedback then please drop a note. 

2 comments :

--j aneiros said...

Hello,

What do you consider "the latest version"? See here: https://docs.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-2017

Javin Paul said...

Hello --j anerios, I was using SQL Server 2016, nice to see that SQL Server 2017 has TRIM() method which means no need for this hack. If you are lucky to be using SQL Server 2017 on your company, better use TRIM() for removing leading and trailing whitespace. Thanks for sharing.

Post a Comment