Cleanup trailing spaces from multiple fields

Posted on 2012-08-25
Medium Priority
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

Anthony Perkins earned 2000 total points
ID: 38333291
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(t.name) + ' SET ') + c.name + '= LTRIM(RTRIM(' + c.name + '))'
FROM	sys.tables t
	INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE	t.name = 'Product'
	AND c.user_type_id IN (167,175,231,239)  -- Only pick varchar, char, nvarchar, nchar


Open in new window


Author Closing Comment

ID: 38333533
Worked perfect the first time. Thank you very much.

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview

862 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