sql server - Remove space recursively in string -

i trying remove multiple white spaces table, contains 40+ columns string values in , 150k rows.

using cursor, came following solution (as part of stored procedure populates table), not remove white spaces in single run. if run update statements alone manually multiple times, spaces removed completely. idea on how can clean data in single run?

declare @col nvarchar(128) declare stringcol cusrsor   select column_name   information_schema.columns    table_name = 'tablename'      , data_type = 'varchar' open stringcol fetch next stringcol @col; while @@fetch_status = 0            begin      update  tablename      set     @col = ltrim(rtrim(replace(@col, '  ', ' ')))         @col '%  %'      fetch next stringcol @col end  close stringcol deallocate stringcol  end 

if have sql server 2016 can use string_split or similar udf functions other versions. trick convert words columns, take non-space , bring them 1 value

declare @col nvarchar(128) declare stringcol cusrsor   select column_name   information_schema.columns    table_name = 'tablename'      , data_type = 'varchar' open stringcol fetch next stringcol @col; while @@fetch_status = 0            begin      update  tablename      set     @col = convert(varchar(max),                 (                  select value + ' '                     string_split (@col, ' ')                   value <>'' xml path('')                )             )        @col '%  %'      fetch next stringcol @col end  close stringcol deallocate stringcol  end 

if use pre-sql server 2016, udf string_split function:

create function [dbo].[string_split] (     @string nvarchar(max),      -- string parsed     @delm nchar(1)=',' ) returns @table table (/*id int identity(1,1), */ value nvarchar(max)) begin     insert @table(value)      select ltrim(t.val.value('.', 'varchar(100)'))  value       from(             select convert(xml, '<m>' + replace(@string, @delm, '</m><m>') + '</m>') x          ) cross apply x.nodes ('/m') t(val)     return end