Remove all spaces from a string in SQL Server

[Originally Posted By]: http://stackoverflow.com/questions/10432086/remove-all-spaces-from-a-string-in-sql-server

What is the best way to remove all spaces from a string in SQL Server 2008?

LTRIM(RTRIM(‘ a b ‘) would remove all spaces at the right and left of the string, but I also need to remove the space in the middle.

shareedit

Simply replace it;

SELECT REPLACE(fld_or_variable, ' ', '')

Edit Just to clarify; its a global replace, there is no need to trim() or worry about multiple spaces:

create table #t (c char(8), v varchar(8))
insert #t (c, v) values 
    ('a a'    , 'a a'    ),
    ('a a  '  , 'a a  '  ),
    ('  a a'  , '  a a'  ),
    ('  a a  ', '  a a  ')

select
    '''' + c + '''' [IN], '''' + replace(c, ' ', '') + '''' [OUT]
from #t  
union all select
    '''' + v + '''', '''' + replace(v, ' ', '') + ''''
from #t 

IN            OUT
'a a     '   'aa'
'a a     '   'aa'
'  a a   '   'aa'
'  a a   '   'aa'
'a a'        'aa'
'a a  '      'aa'
'  a a'      'aa'
'  a a  '    'aa'
shareedit
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