CODEWITHSUNDEEP
sqltrimtemp-tables
user715993
user715993

Reputation: 311

Trim All columns in a temp table and Select data

Got a quick question.

I first select a dataset INTO a temp table.

The data contained in the columns of the temp table, contains extra spaces in them. Many columns exist with extra spaces. So, I googled to check how to remove all spaces in the columns.

Found this solution SQL Server: How to perform Rtrim on all varchar columns of a table

But, I tried to implement the same for the temp table, but it does not work as it is, as the temp tables are created in a different schema.

So, I modified the same and tried. But no luck. Can someone point me, where I am doing it wrong?

The code is as follows:

DECLARE @Op NVARCHAR(1000)
DECLARE @table_name AS VARCHAR(300) 

SET @table_name = (SELECT TOP 1 [name] FROM tempdb..sysobjects WHERE name LIKE '#tempFinalResults%')

SET @Op = ' UPDATE ' + @table_name + ' SET COLUMN_NAME = LTRIM(RTRIM(COLUMN_NAME)) FROM tempDB.information_Schema.Columns WHERE DATA_Type IN (''varchar'', ''char'', ''nchar'', ''nvarchar'')'

Exec sp_executesql @Op

Select * from #tempFinalResults

Thanks guys. Appreciate it.

thanks, Shawn

Upvotes: 1

Views: 4716

Answers (2)

PaulStock
PaulStock

Reputation: 11283

Your problem is that you have to build the list of column names with a SELECT statement so that it loops through all columns.

I think this might work:

DECLARE @Op NVARCHAR(3000)
DECLARE @table_name AS VARCHAR(300) 

SET @table_name = (SELECT TOP 1 [name] FROM tempdb..sysobjects WHERE name LIKE '#tempFinalResults%')

SELECT @Op = COALESCE(@Op + ',[', '[') + COLUMN_NAME +
    '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))' 
FROM 
    tempDB.information_Schema.Columns 
WHERE 
    DATA_Type IN ('varchar', 'char', 'nchar', 'nvarchar')

SET @Op = 'UPDATE [' + @table_name + '] SET ' + @Op

Exec sp_executesql @Op

Upvotes: 1

Jennifer S
Jennifer S

Reputation: 1449

If you know which columns need to be trimmed, you could trim them as part of your select into the temp table, as in 

SELECT 
ID,
RTRIM(myCharColumn1) as myColumn1,
RTRIM(myCharColumn2) as myColumn2,
RTRIM(myCharColumn3) as myColumn3
INTO #myTempTable
WHERE.....

Upvotes: 2

Related Questions