Cleanup trailing spaces from multiple fields

Posted on 2012-08-25
Last Modified: 2012-08-25
I am in need of a solution to a problem which I have inadvertently created during my build of my MS SQL 2008 database. I have multiple tables, each with multiple fields which may contain data with trailing and possibly leading spaces. I am looking for a function/procedure/statement which will loop through every char/nvarchar field and eliminate the offending spaces.

I am aware that I can individually run Update statements using RTRIM/LTRIM for each field name. However, because I have hundreds of such fields I am hoping that someone has a more automated way to cleanup a whole table once. It would most likely process field 1 through field n and if the field is char/nvarchar then the update statement will execute for that field, otherwise it will go to the next field.

Question by:richardandro
    LVL 75

    Accepted Solution

    Assuming you are not using the deprecated data types text or ntext, you could build a SQL Script to do it as in:
    DECLARE @SQL nvarchar(1000)
    SELECT	@SQL = ISNULL(@SQL + ', ', 'UPDATE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME( + ' SET ') + + '= LTRIM(RTRIM(' + + '))'
    FROM	sys.tables t
    	INNER JOIN sys.columns c ON t.object_id = c.object_id
    WHERE = 'Product'
    	AND c.user_type_id IN (167,175,231,239)  -- Only pick varchar, char, nvarchar, nchar

    Open in new window


    Author Closing Comment

    Worked perfect the first time. Thank you very much.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now