Ananth Ananth - 6 months ago 22
SQL Question

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.

Answer

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'