Remove all spaces from a string in SQL Server

Solution 1:

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 for either char or varchar:

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 

Result

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"

Solution 2:

I would use a REPLACE

select REPLACE (' Hello , How Are You ?', ' ', '' )

REPLACE

Solution 3:

If it is an update on a table all you have to do is run this update multiple times until it is affecting 0 rows.

update tableName
set colName = REPLACE(LTRIM(RTRIM(colName)), '  ', ' ')
where colName like '%  %'

Solution 4:

100% working

UPDATE table_name SET  "column_name"=replace("column_name", ' ', ''); //Remove white space

UPDATE table_name SET  "column_name"=replace("column_name", '\n', ''); //Remove newline

UPDATE table_name SET  "column_name"=replace("column_name", '\t', ''); //Remove all tab

You can use "column_name" or column_name

Thanks

Subroto