Remove all spaces from a string in SQL Server
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"
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 tableNameset colName = REPLACE(LTRIM(RTRIM(colName)), ' ', ' ')where colName like '% %'