Thursday, May 4, 2017

SQL: Trimming all the column values using LTRIM and RTRIM dynamically

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)


No comments:

Post a Comment