In previous article, I mentioned Best Open Source Database Software (DBMS) and how to Get Date From Datetime in SQL Server but now in this article, we will discuss about TRIM(), LTRIM(), RTRIM() Functions in SQL Server with examples, which is used to remove extra space from string or special character from string.
The TRIM function of SQL Server is designed to remove leading and trailing whitespaces from a character string, whiel LTRIM() will trim only leading characters from string, and RTRIM() will trim ending characters from string.
TRIM() Function with an example
TRIM was introduced in SQL Server 2017, before that we had LTRIM and RTRIM functions to trim leading and ending spaces from string, but using TRIM() we can remove whitespace from both sides of string easily.
Example:
SELECT TRIM(' QA With Experts ')Output:
Remove Other characters from string using TRIM in SQL Server
You can also remove other characters from string not only just space, suppose we want to remove "@" from both side of string using TRIM() then you can use below example
SELECT TRIM( '@ ' FROM '@Hello World@')Output:
Hello WorldLTRIM() function with an example
If you are using older version than SQL Server 2017, then you cannot use TRIM() function, so we should know about LTRIM() and RTRIM() functions, so let's understand LTRIM() first, which is used to remove space from leading side of string.
Example:
SELECT LTRIM (' Hello World')Output:
Hello WorldRTRIM() function with an example
RTRIM() is used to remove trailing spaces from string in SQL Server as explained above, so here is the example of using it.
SELECT RTRIM ('Hello World ')
Output:
Hello WorldTrim a string in older than SQL Server 2017 Version
If you want to trim both leading and trailing spaces of a string SQL Server before 2017 Version, then you can combine both, LTRIM() and RTRIM() both in one statement of SQL Server and then remove leading/trailing space from string.
DECLARE @Name nvarchar(100) = ' Using LTRIM RTRIM '
SELECT LTRIM(RTRIM(@Name))Output:
Using LTRIM RTRIMSo, in the above example, we have declared and initialized a string value with spaces before or after of text.
Which is then removing by calling RTRIM first, which will remove trailing space from string, then by calling LTRIM, which will remove starting space from string.
You may also like to read:
How to check sql server version? (Various ways explained)
Stored procedure in sql server (With Example)
Buy us a coffee
Become a Patron