Trimming Text Columns Using INFORMATION_SCHEMA System Views

By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

March 3, 2007

You may have wondered many times what are the INFORMATION_SCHEMA views doing in the database?  You could just go to the system tables or system views in SQL Server 2005 and find the same information.  Well they are ANSI  SQL-92 requirements. If you use them, you can transport your code to Oracle and IBM DB2.  Well, more or less. The extensions (WHILE, IF) to SQL in T-SQL are not covered by the ANSI standard, so you are on your own in converting those. Neither some of the special functions such as dynamic SQL.

However, since Microsoft SQL Server 2005 supports the ANSI standard, you can safely use the INFORMATION_SCHEMA views. The following examples illustrate one use.

Assume you want to left and right trim all the text columns in a table. Instead of hard-wiring several updates, you can just build an SQL string and execute it dynamically.

Here is the code:

USE AdventureWorks

--Single table trim
declare @SQL nvarchar(max)
SET @SQL = ''
SELECT @SQL = @SQL+'Update '+
Table_Schema+'.'+
Table_Name +' SET '+
Column_Name +' = Ltrim(RTrim( '+ Column_Name +'))'+';'+
CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME ='Contact'AND
Data_Type IN('char','varchar','nchar','nvarchar')

PRINT @SQL
EXEC (@SQL)

The resultant SQL string:

Update Person.Contact SET Title = Ltrim(RTrim( Title));
Update Person.Contact SET FirstName = Ltrim(RTrim( FirstName));
Update Person.Contact SET MiddleName = Ltrim(RTrim( MiddleName));
Update Person.Contact SET LastName = Ltrim(RTrim( LastName));
Update Person.Contact SET Suffix = Ltrim(RTrim( Suffix));
Update Person.Contact SET EmailAddress = Ltrim(RTrim( EmailAddress));
Update Person.Contact SET Phone = Ltrim(RTrim( Phone));
Update Person.Contact SET PasswordHash = Ltrim(RTrim( PasswordHash));
Update Person.Contact SET PasswordSalt = Ltrim(RTrim( PasswordSalt));

To update all the tables in a schema, we have to join with the INFORMATION_SCHEMA.TABLES view:

--Trim all tables in a schema
declare @SQL nvarchar(max)
SET @SQL = ''
SELECT @SQL = @SQL+'Update '+
c.Table_Schema+'.'+
c.Table_Name +' SET '+
Column_Name +' = Ltrim(RTrim( '+ Column_Name +'))'+';'+
CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = T.TABLE_CATALOG
and c.TABLE_SCHEMA = T.TABLE_SCHEMA
and c.TABLE_NAME = T.TABLE_NAME
WHERE c.TABLE_SCHEMA ='Person'AND
Data_Type IN('char','varchar','nchar','nvarchar')
and TABLE_TYPE='BASE TABLE'

PRINT @SQL
EXEC (@SQL)

The resultant SQL string:

Update Person.Address SET AddressLine1 = Ltrim(RTrim( AddressLine1));
Update Person.Address SET AddressLine2 = Ltrim(RTrim( AddressLine2));
Update Person.Address SET City = Ltrim(RTrim( City));
Update Person.Address SET PostalCode = Ltrim(RTrim( PostalCode));
Update Person.AddressType SET Name = Ltrim(RTrim( Name));
Update Person.Contact SET Title = Ltrim(RTrim( Title));
Update Person.Contact SET FirstName = Ltrim(RTrim( FirstName));
Update Person.Contact SET MiddleName = Ltrim(RTrim( MiddleName));
Update Person.Contact SET LastName = Ltrim(RTrim( LastName));
Update Person.Contact SET Suffix = Ltrim(RTrim( Suffix));
Update Person.Contact SET EmailAddress = Ltrim(RTrim( EmailAddress));
Update Person.Contact SET Phone = Ltrim(RTrim( Phone));
Update Person.Contact SET PasswordHash = Ltrim(RTrim( PasswordHash));
Update Person.Contact SET PasswordSalt = Ltrim(RTrim( PasswordSalt));
Update Person.ContactType SET Name = Ltrim(RTrim( Name));
Update Person.CountryRegion SET CountryRegionCode = Ltrim(RTrim( CountryRegionCode));
Update Person.CountryRegion SET Name = Ltrim(RTrim( Name));
Update Person.StateProvince SET StateProvinceCode = Ltrim(RTrim( StateProvinceCode));
Update Person.StateProvince SET CountryRegionCode = Ltrim(RTrim( CountryRegionCode));
Update Person.StateProvince SET Name = Ltrim(RTrim( Name));