I have found this useful when importing certain text files into a staging table prior to appending main tables, and when it is typical that many of the imported text columns are padded with leading or trailing spaces.
Naturally, this can also be handled in VBScript processes during DTS as part of the import steps...but still...this comes in handy at times.
Passing a table name to the proc builds an Update statment to update the table char/varchar/nvarchar/nchar type columns with a trim statement.
Not sure if it can be done without a cursor..but it is effective
Works in both 2000/2005
It is most beneficial for leading spaces and for the char type column.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Proc [dbo].[pTrimCharacterFields](@TableName varchar(100))
As
DECLARE @SQLstring varchar(8000)
DECLARE @firstTime bit
-- Start building our UPDATE statement
SELECT @SQLstring = 'UPDATE ' + @TableName +' SET '
SELECT @firstTime = 1
-- Get a list of character columns in this table
DECLARE getColumnsCursor CURSOR
READ_ONLY
FOR
SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo' AND
c.TABLE_NAME = @TableName AND
c.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar')
ORDER BY c.COLUMN_NAME
DECLARE @columnName nvarchar(128)
OPEN getColumnsCursor
FETCH NEXT FROM getColumnsCursor INTO @columnName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
IF (@firstTime = 0)
SELECT @SQLstring = @SQLstring + ','
-- append our column to the UPDATE statement
SELECT @SQLstring = @SQLstring + '[' + @columnName + ']=LTRIM(RTRIM([' + @columnName + ']))'
SELECT @firstTime = 0
END
FETCH NEXT FROM getColumnsCursor INTO @columnName
END
CLOSE getColumnsCursor
DEALLOCATE getColumnsCursor
EXEC(@SQLstring)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
Here is a test of it.
Create Table TrimTest (ColumnA char(10) not null,
ColumnB nvarchar(10) not null,
ColumnC varchar(10) not null,
ColumnD nchar(10) not null)
Insert Into TrimTest(ColumnA,ColumnB,ColumnC, ColumnD)
Select ' TestA ', ' Test B ', ' TestC ', 'A 1 '
Select ColumnA,Len(ColumnA) as LenA,
ColumnB, Len(ColumnB) as LenB,
ColumnC,len(ColumnC) as LenC,
ColumnD,len(ColumnD) as LenD
FROM TrimTest
Exec dbo.pTrimCharacterFields 'TrimTest'
Select ColumnA,Len(ColumnA) as LenA,
ColumnB, Len(ColumnB) as LenB,
ColumnC,len(ColumnC) as LenC,
ColumnD,len(ColumnD) as LenD
FROM TrimTest
Drop Table TrimTest
Poor planning on your part does not constitute an emergency on my part.
Proc to trim leading/trailing spaces-all char cols
Posted - 2008-03-09 : 12:16:17