SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Proc to trim leading/trailing spaces-all char cols
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 03/09/2008 :  12:16:17  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.


Edited by - dataguru1971 on 03/09/2008 12:17:23
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000