How to Trim Leading and Trailing spaces for all the values in all the Columns in all the Tables in a SQL Server Database- SQL Server / TSQL Tutorial

Scenario: Download Script

You are working as Database Developer / SQL server Developer or T-SQL developer. The project you are working is to find and clean the data in database. You got this scenario where you have to find all the records in a database with leading or trailing spaces and then update that record. 

You need to check the values for all the columns which are char, nchar, varchar, nvarchar, text or ntext data type.

Solution:

We will be using cursor to find out all the records with leading or trailing space and then update them.We will trim the empty spaces from each value. As the process has to be done for each column, this can take long time to run if you your database is big and on top of that you have a lot of records which needs to be updated. 

Here are few suggestions
1) Comment out the Update statement from below code and only run the select, so get the record count how many records will be updated. Also you can verify the records to make sure.
2) Take the backup of database and restore in QA or UAT and run the script and validate all the results, By doing that you will get to know how much time it took for entire script to run.
3) if your script took long time, you can schedule it on weekends or off hours when you have less activity on SQL Server, specially on that server.

here is the code.

USE YourTestDB
GO


Declare @UpdateTo VARCHAR(100)
DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
Declare @DataType VARCHAR(50)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
     DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,ColumnDataType VARCHAR(50)
    ,TotalTableRowCount INT
    ,UpdatedRecordCount INT
    )

DECLARE Cur CURSOR
FOR
SELECT C.Table_CataLog
    ,C.Table_Schema
    ,C.Table_Name
    ,C.Column_Name
    ,'[' + C.Table_CataLog + ']' + '.[' + C.Table_Schema + '].' 
    + '[' + C.Table_Name + ']' AS FullQualifiedTableName,
    C.Data_Type
FROM information_schema.Columns C
INNER JOIN information_Schema.Tables T ON C.Table_Name = T.Table_Name
    AND T.Table_Type = 'BASE TABLE'
    and (C.Data_Type like '%CHAR%'
    or C.Data_Type like '%Text%')


OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName
    ,@DataType

WHILE @@FETCH_STATUS = 0
BEGIN
--Handle Text or NText Columns
    
    DECLARE @ColumnName1 VARCHAR(50)
    IF @DataType like '%Text%'
    BEGIN 
    SET @ColumnName1='CAST('+@ColumnName + ' AS NVARCHAR(MAX))'
    END 
    ELSE
    BEGIN 
    SET @ColumnName1=@ColumnName
    END


    DECLARE @SQL NVARCHAR(MAX) = NULL
    DECLARE @UpdateSQL NVARCHAR(MAX)
    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' 
    + @SchemaName + ''' AS TableName,
      ''' + @TableName + ''' AS SchemaName,
      ''' + @ColumnName + ''' AS ColumnName,
      ''' + @DataType + ''' AS ColumnName,
      (Select count(*) from ' + @FullyQualifiedTableName + ' With (Nolock)) 
      AS TotalTableRowCount,
      count(*) as UpdatedRecordCount from ' + @FullyQualifiedTableName 
      + 'With (Nolock) Where  '+@ColumnName+' like ''% '''+
      'or '+@ColumnName+' like '' %'''


    Print @SQL
    INSERT INTO #Results
    EXEC (@SQL)

     --Prepare and Execute Update Statement
      SET @UpdateSQL='Update '+@FullyQualifiedTableName+'
                 SET '+@ColumnName+'=LTRIM(RTRIM('+@ColumnName1+'))'+
                 ' Where  '+@ColumnName+' like ''% '''+
                 'or '+@ColumnName+' like '' %'''

                 Print @UpdateSQL
                 EXEC(@UpdateSQL)
   

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
        ,@DataType
END
CLOSE Cur
DEALLOCATE Cur

SELECT *
from #Results
Where UpdatedRecordCount<>0
--drop table #Results


The query will also return you Table Name, Schema Name, Column Name, Column Type, Total Table Record Count and Update Record Count.
How to remove leading and trailing spaces for all the columns in all the tables in SQL Server Database

 Check out our other posts on Data Analysis / Data Validation / Data Cleansing

8 comments:

  1. The easiest way to gently amend your soil, is to allow a tree to take back its own nutrients, by allowing leaves to compost themselves on-site. Stump Grinding

    ReplyDelete
  2. Since wood grains normally vary, no two wood shake rooftops are the equivalent. In spite of the fact that there have been impersonation wood shake shingles produced using black-top shingles, these shingles won't ever effectively copy the smooth nature of regular wood shakes.Vietnam sourcing

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. I cannot thank you enough for the blog. Really looking forward to read more. Really Great.
    laser trimming price

    ReplyDelete
  5. I truly appreciate this blog article. Really looking forward to read more. Much obliged. laser trimming to buy

    ReplyDelete
  6. Thanks so much for the article post. Really Cool.
    price of laser trimming

    ReplyDelete
  7. This is one awesome blog article. Really thank you! Will read on...
    best laser trimming

    ReplyDelete