Remove spaces of a string in SQL Server


Recently, I was facing one issue while trying to remove leading and trailing space using LTRIM and RTRIM SQL function.

Normally, When you want to remove leading and trailing spaces from column of a table, you just use below query:

Select LTRIM ( RTRIM ( ColumnName ) ) FROM TableName

But In my case I was not able to remove trailing spaces from column using above query. So I have investigated the issue and came to know that if my column contains Line feeds, carriage returns or tabs than TRIM functions can not able to remove it. So we need to use below query:

Select LTRIM ( RTRIM ( REPLACE ( REPLACE ( REPLACE ( ColumnName, CHAR(10), ''), CHAR(13), ''), CHAR(9), '') ) )  FROM TableName

There also some other characters which are not printable. Such characters needs to be replaced with Blank character before we use TRIM Functions. List of such characters are :

--NULL
Replace(ColumnName,CHAR(0),'');
--Horizontal Tab
Replace(ColumnName,CHAR(9),'');
--Line Feed
Replace(ColumnName,CHAR(10),'');
--Vertical Tab
Replace(ColumnName,CHAR(11),'');
--Form Feed
Replace(ColumnName,CHAR(12),'');
--Carriage Return
Replace(ColumnName,CHAR(13),'');
--Column Break
Replace(ColumnName,CHAR(14),'');
--Non-breaking space
Replace(ColumnName,CHAR(160),'');

Hope this will help !!!

Jay Ganesh

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s