RTRIM(RTRIM(ALLCOLUMNS OF A TABLE))

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

SQL Server 2005 Forums

Transact-SQL (2005)

RTRIM(RTRIM(ALLCOLUMNS OF A TABLE))

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2010-02-22 : 23:10:45

Hi
I need to make a rtrim(ltrim(column)) to all columns of a table.

What is the best way?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-22 : 23:18:41

The best way is to fix the code that is causing all of these extra spaces. You should not have to use RTRIM and LTRIM on every column for a table.

You could script it out using the INFORMATION_SCHEMA.COLUMNS view along with a little dynamic SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

shifis
Posting Yak Master

157 Posts

Posted - 2010-02-22 : 23:32:36

You are right, the data come from an instruction BULK INSERT, the SP put the data that come from a txt into a table where all the columns are 255 long. Then it started to made validations, but each time that this happen they uses rtrim(ltrim(column)).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 02:04:06

IF you use varchar, leading spaces are truncated automatically. If datatype is char , you should use rtrim

Try this

declare @sql varchar(1000)
set @sql=''
select @sql=@sql+'ltrim(rtrim('+column_name+')),' from information_schema.columns
where table_name='table_name'
set @sql='select '+substring(@sql,1,len(@sql)-1)+' from table_name'
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 03:02:15

" IF you use varchar, leading spaces are truncated automatically"

Madhi: That doesn't read right, as I read it anyway!


DECLARE @MyTable TABLE
(
	MyCol varchar(100)
)
INSERT INTO @MyTable
SELECT '   foo bar   '

SELECT ']' + MyCol + '[' FROM @MyTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources