The below SQL Dynamic Query will remove all the white spaces from each column of datatype varchar or nvarchar using LTRIM and RTRIM.
DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'Users'
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND (DATA_TYPE = 'varchar' OR DATA_TYPE = 'nvarchar')
SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
PRINT @SQL
EXEC(@SQL)
DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'Users'
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND (DATA_TYPE = 'varchar' OR DATA_TYPE = 'nvarchar')
SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
PRINT @SQL
EXEC(@SQL)
No comments:
Post a Comment