Proc to trim leading/trailing spaces-all char cols

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Proc to trim leading/trailing spaces-all char cols

Author

Topic

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 12:16:17

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources