MS-SQL script to generate update statements for rtrim and ltrim

Recently I did a bulk insert into a table from a CSV file and this data had trailing spaces. I didn't notice until some email addresses look like weren't valid.

I read this article which explains when the data is trimed or not

Since I had many columns to update I needed to find another solution to write/generate the update statements for "update table set column = rtrim(ltrim(column))"


declare @table_name varchar(150) = 'dbo.Member'
declare @stmt nvarchar(max)



set @stmt =
(
select '[' + c.name + '] = rtrim(ltrim([' + c.name + ']))' + ',' + char(10) + char(9)
from
dbo.syscolumns c
inner join dbo.systypes st ON st.xusertype = c.xusertype
inner join dbo.systypes bt ON bt.xusertype = c.xtype
where
c.id = object_id(@table_name,'U') and (bt.name = 'varchar' or bt.name = 'nvarchar')

for xml path ('')
)
if @stmt is not null
begin

set @stmt = 'update ' + @table_name + char(10) + 'set ' + substring(@stmt, 0, len(@stmt) - 2)
execute sp_executesql @stmt
end

Comments

Popular posts from this blog

IIS 7.5, HTTPS Bindings and ERR_CONNECTION_RESET

Table Per Hierarchy Inheritance with Column Discriminator and Associations used in Derived Entity Types

Verify ILogger calls with Moq.ILogger