Remove All Spaces From a String in SQL Server

There are scenarios of the occurrence of spaces before and after a string and we may need to remove/trim the spaces for our use. Let us see how it is getting handled in SQL Server. Till SQL Server 2016, we have the functions called SQL LTRIM and SQL RTRIM functions. The name itself implies that LTRIM is helpful to remove the leftmost spaces and RTRIM is helpful to remove the rightmost spaces.

From SQL Server 2017 onwards, we have TRIM() function also which trim both leading and trailing characters together with a single function. In this article, let us cover them in detail.

SQL LTRIM function:

It removes the spaces from the beginning of the string. We may be having spaces before and after the string. By using LTRIM() function, we can remove leftmost spaces. Let us use DATALENGTH() function to calculate data length in bytes before and after using SQL LTRIM function. Combining all these, let us see with the below example

Query:

DECLARE @gfgString VARCHAR(26)= 
        '     GeeksForGeeks       ';

SELECT @gfgString as OriginalString, 
       LTRIM(@gfgString) AS StringAfterLTRIM, 
       DATALENGTH(@gfgString) AS 'DataLength String (Bytes)', 
       DATALENGTH(LTRIM(@gfgString)) AS
       'DataLength String (Bytes) After LTRIM';

Output:

SQL RTRIM function:

It removes the spaces from the end(starting from the right side) of the string. We may be having spaces before and after the string. By using RTRIM() function, we can remove the rightmost spaces. Let us use DATALENGTH() function to calculate data length in bytes before and after using SQL RTRIM function. Combining all these, let us see with the below example

Query:

DECLARE @gfgString VARCHAR(26)=
        '     GeeksForGeeks       ';

SELECT @gfgString as OriginalString, 
       RTRIM(@gfgString) AS StringAfterRTRIM, 
       DATALENGTH(@gfgString) AS 'DataLength String (Bytes)', 
       DATALENGTH(RTRIM(@gfgString))
       AS 'DataLength String (Bytes) After RTRIM';

Output:

SQL TRIM() function:

From SQL Server 2017 onwards, we have TRIM() functionality also. It removes the beginning spaces as well as end spaces of the string.

Query:

DECLARE @gfgString VARCHAR(26)= 
        '     GeeksForGeeks       ';
SELECT @gfgString as OriginalString, 
       TRIM(@gfgString) AS StringAfterTRIM, 
       DATALENGTH(@gfgString) AS 'DataLength String (Bytes)', 
       DATALENGTH(TRIM(@gfgString))
       AS 'DataLength String (Bytes) After TRIM';

Output:

We can use the above 3 functionalities to remove the spaces according to the requirement. Generally, to remove all the spaces means, we need to go for TRIM(). We can use the same to trim the column values also.

Query:

use GEEKSFORGEEKS
SELECT AuthorName, LTRIM(AuthorName) as 'String after LTRIM', RTRIM(AuthorName) as 'String after RTRIM', 
TRIM(AuthorName) as 'String after TRIM' FROM Authors;

Output:

SQL Server provides many string manipulation functions and here we have seen LTRIM(), RTRIM(), and TRIM() functions to remove the spaces.