Welcome to EMC Consulting Blogs Sign in | Join | Help

Jim 2.0

SQL: Using the Trim function to remove white space from a field

Today I was looking at a table with an apparantly empty column and discovered that the field is actually populated with blank spaces - Fortunately this field is of no interest to me and I noticed it purely by chance when I pasted an extract of the dat into excel and happened to notice the cell was unusually wide when I formatted the sheet. Anyway, it reminded me of an issue that is is often worth looking out for of trailing (or I suppose preceeding) spaces.

 Fortunately this is easily solved with the trim function, which will remove white spaces from a field

SELECT  TRIM(fieldname)
,            LTRIM(fieldname)
,            RTRIM(fieldname)
,            LTRIM(RTRIM(fieldname))
FROM     tablename

Depending on the particular 'flavour' of SQL being used:
TRIM() will remove both leading and trailing white spaces;
LTRIM() removes leading white spaces (i.e. from the beginning of a string);
RTRIM() removes trailing white space (i.e. from the end of a string);
If TRIM() is not supported, then LTRIM(RTRIM()) will achieve the same result.

As I said, this is a useful way to get rid of pesky additional spaces, however just because it can be done, it does not necessarily mean it should be done, since additional functions such as this have a cost in terms of performance to the query. One temptation is to apply the function to every field you select in a statement, but this is not a good idea, instead simply bare it in mind as a useful trick for when it is necessary to remove these white spaces.

 James

Published Tuesday, August 14, 2007 4:19 PM by James.Pipe

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mohit Nayyar said:

Yes, Makes sense.....But just remember one more thing........there is a huge difference between empty spaces and NULL...........so while evaluating the result set make sure you check for NULL as well.

You can do this using IsNull or IS NULL or IS NOT NULL in Microsoft SQL Server 2005.

Mohit Nayyar

August 15, 2007 12:48 AM
 

briana said:

I don't get it,

how would I put it in the code of the video?

September 27, 2007 3:20 PM
 

James.Pipe said:

I'm not sure what your question is; what video?

September 28, 2007 1:38 PM
 

Suma said:

How to remove the white spaces in between the words?

October 9, 2007 6:46 AM
 

James.Pipe said:

Try this: replace(@text,' ','')

This replaces any space in a text string with no space

Thanks,

James

October 9, 2007 1:39 PM
 

Erika Aisyah said:

Thank you for your info for truncating all trailing blanks! very useful

SELECT     fieldname

FROM         tablename

WHERE     (LTRIM(RTRIM(fieldname)) = 'Approve')

October 30, 2007 8:37 AM
 

Easton said:

Thank you James.Pipe for the code to remove middle spaces. Very useful!

replace(@text,' ','')

March 25, 2008 1:30 AM
 

Lekshmi said:

Thank you James. It worked for my requirement.

June 17, 2008 10:12 AM
 

GG said:

Great James the code replace(@text,' ','')  it's what i was looking for.

Thanks a lot!!!

June 18, 2008 3:20 PM
 

Abhinandan Singh said:

I have to remove space from the string '8000002008Q1 0001'.Space is at the 13th spot . I m working on d2k.

June 23, 2008 12:46 PM
 

Abhinandan Singh said:

I have to remove space from the string '8000002008Q1 0001'.Space is at the 13th spot . I m working on d2k. Please it is urgent

June 23, 2008 12:46 PM
 

Mogamad Allie said:

Hi Abhinandan

The code replace above your insert will still work eg

select replace('8000002008Q1 0001',' ','')

Will retrun the string without the space.

July 2, 2008 4:41 PM
 

Lost in space said:

If I am updating the table and I want to replace '' with NULL do I have to use a CASE? (CASE WHEN ltrim(rtrim([Field])) = '' THEN NULL

August 22, 2008 9:26 PM
 

NADEEM said:

MANY MANY THANKS

September 9, 2008 12:17 PM
 

k_lib said:

can you do something like this?

 select TRIM(*)

from DB

I know it won't work but i didn't know if maybe there was a easy way.

December 11, 2008 5:46 PM
 

Saravanan said:

mghjghjghj

April 21, 2009 10:59 AM
 

Marla said:

OK, for Abhinandan Singh it seems pretty straight forward, go into the cell and delete the 13th spot yourself, but does anyone know how to remove any blanks not just within once cell, but within a whole column with different data?

Thanks

April 28, 2009 5:45 PM
 

Nigel said:

UPDATE table_name

SET field_name=REPLACE(REPLACE(REPLACE(field_name,char(9),''),char(10),''),char(13),'')

To remove returns (13), new lines (10) and tabs (9). Quite slow but it was only run once to correct something.

May 5, 2009 2:56 PM
 

kEL said:

replace(@TEXT,' ','')

THANKS, PERFECT!

November 23, 2009 5:28 PM
 

Ashish Srivastava said:

Thanks Friends .....

It Gave me Help and i solved my problem......Thaks Again

December 24, 2009 6:04 AM
 

Gopinath Subbaian said:

Thanks James

June 29, 2010 6:58 AM
 

prasad said:

Thanks great article keep it up

July 19, 2010 1:55 PM
 

Dao said:

Hi ,

Thats a great article , but I have one more question; how to eliminate the wite spaces of all fields should I

select TRIM(*) from xyz ;

Thankns for your help

September 1, 2010 8:52 AM
 

SQL User said:

Thanks James this came in to be very handy for me !

September 22, 2010 6:49 PM
 

jeffry said:

how to remove blank space n between words ir sentece is

i 'want' to 'remove' space in pl/sql

November 23, 2010 2:12 PM
 

JOHN MANAVALAN said:

How to replace 'X           X                     X                  X' to 'X X X X'

May 26, 2011 1:33 PM
 

Simon G said:

Hi John

Had the same problem and solved it with this function:

Create

function dbo.RemoveSpaceBetween (@strInput nvarchar(max))

Returns nvarchar(max)

As

Begin

declare @strOutput nvarchar(max)

set @strOutput = @strInput

while CHARINDEX ('  ', @strOutput) > 0

set @strOutput = replace( @strOutput,'  ', ' ')

return @strOutput

End

Go

Select dbo.RemoveSpaceBetween('sss              ff  fff')

August 8, 2011 10:13 AM
 

John said:

Please could you help me with code to remove all spaces at the beginning and end of every column. For example;

col1       col2       col3       col4

John      Brook     16         New drive

Ice        Cook       3          Bincroft Garden

should be

JohnBrook16New drive

IceCook3Bincroft Garden

Thanks in advance

September 30, 2011 10:01 AM
 

Balu said:

Hi John,

   Use trim function to remove white space in start and end position and use pipe symbol for concatenation.

 eg: select trim(col1)||trim(col2)||trim(col3)||trim(col4) from tablename

October 20, 2011 1:09 PM
 

Balu said:

Hi JOHN MANAVALAN,

 Replace 'X           X                     X                  X' to 'X X X X'

Try this..

SELECT REGEXP_REPLACE('X           X                     X                  X', '( ){2,}', ' ') RESULT FROM dual;

October 20, 2011 1:14 PM
 

Shoki said:

Thank you!! The replace(@text,' ','') worked like a charm!!!!

February 28, 2012 12:30 PM
 

@john said:

it worked ya tanks a lot;

June 15, 2012 2:22 PM
 

pankaj said:

if one table including 10 fields, i want to use trim function means in single query i want to trim all the 10 fields?

November 3, 2012 4:07 PM
 

satish said:

Hi All,

I have applied the above mentioned method

str_replace(str_replace(str_replace(str_replace(str_replace(str_replace(column,char(10),''),char(13),'')char(13)+char(10),"),char(9),")char(11),")char(32),")

but then also for just two columns i am getting a  newline in unix generated data files.

Kindly provide with any solution as i m stuck and need to getit working..

Regards

January 28, 2013 1:56 PM
 

satish said:

i m using sybase db and generating files with unix using kisql..

January 28, 2013 1:57 PM
 

Steve said:

It's "bear in mind", BTW.  Carry, not undress. :-)

January 30, 2013 2:04 PM
 

Sumeet said:

Superb!!!

February 26, 2013 11:50 AM
 

jeff said:

doesn't work for my case:

-------------------------------------------------------------------------

xxx hg         ghv  fc  s

      kjln   gh aa gj                                                 kjncbf

ghgfv      rhgge

--------------------------------------------------------------------------

thats the text that i want to remove spaces from to remain with a single space/normal space between any two texts. Any idea?

April 17, 2013 12:09 PM
 

Russ said:

Excellent! Just what I was looking for!

May 20, 2013 4:36 PM
 

toko sepatu said:

Thanks alot for this. Made my state address table perfect.

June 8, 2013 4:20 AM
 

nc65 said:

Either method will work, see this examples..

Update Staging_Asset_Level

SET [site address] =  RTrim(LTrim([site address]))

OR

Update Staging_Asset_Level

SET [site address] = REPLACE([site address],'  ','')

WHERE CHARINDEX('  ', [site address]) <> 0

Both with do the same thing.. Remove before and after blank spaces..!

Have fun!

June 23, 2013 4:56 PM
 

Raj said:

How to  Removing ALL extra spaces 'within' a string?

December 27, 2013 6:30 AM
 

mahalakshmi said:

the word is maha lakshmi

i remove the white space in center

how do this?

February 18, 2014 8:12 AM
 

Paulk said:

You need to define White space.

Whitespace is the following characters, space, CR, HT ,FF, LF, VT.

http://en.wikipedia.org/wiki/Whitespace_character

your solution removes spaces but not whitespace.

September 17, 2014 3:56 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit
Powered by Community Server (Personal Edition), by Telligent Systems