• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

truncate white spaces

Hi There,

How to remove or truncate white spaces in all the columns for a table? I am using sql server 2005

Thanks.
0
theartha
Asked:
theartha
2 Solutions
 
for_yanCommented:
I think this should work  
trim(yourcolumn)
but it will keep white spaces inside the column

0
 
for_yanCommented:
select trim(yourcolumn1) yourcolumn1, trim(yourcolumn2) yourcolumn2... from table_name
0
 
thearthaAuthor Commented:
@for_yan:
I got this error message:
'trim' is not a recognized built-in function name
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
ralmadaCommented:
you should use

ltrim(rtrim(yourcolumn))
0
 
for_yanCommented:
Well frankly I'm using Oracle, but I checked on SQL server
forums and they all write Trim

http://www.cftips.net/post.cfm/sql-trim-functions-remove-white-space

Maybe on SQL server it is case sensitive and you want   Trim
0
 
radcaesarCommented:
DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'YourTableName'

SELECT @SQL = COALESCE(@SQL + ',[', '[') +
              COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
    AND DATA_TYPE = 'varchar'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
PRINT @SQL

Also, You can get all table in runtime and loopthrough

SELECT * FROM sys.Tables
0
 
for_yanCommented:

Well, according to this:
http://www.cftips.net/post.cfm/sql-trim-functions-remove-white-space
and many other sources on internet  there should be Trim along with left trim (ltrim) and right trim(rtrim)
0
 
ralmadaCommented:
@for_yan

TRIM is not a valid function in t-sql. you need to use both LTRIM or RTRIM if you want to have the same effect.

The below is the list of valid string function in SQL server, TRIM is no where to be found.
http://msdn.microsoft.com/en-us/library/ms181984.aspx
0
 
for_yanCommented:
I see...
Strange, that all those folks on internet are mentioning it.
0
 
ralmadaCommented:
the link that you're mentioning seems to be a Coldfusion forum, Probably in Coldfusion there's a trim function.
0
 
for_yanCommented:
Yes, I see, so RTRIM(LTRIM(...)) is the way to go.
So, after all I'm right that I stick to Oracle :)
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now