?
Solved

truncate white spaces

Posted on 2011-04-25
11
Medium Priority
?
406 Views
Last Modified: 2012-05-11
Hi There,

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

Thanks.
0
Comment
Question by:theartha
11 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 35461446
I think this should work  
trim(yourcolumn)
but it will keep white spaces inside the column

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35461451
select trim(yourcolumn1) yourcolumn1, trim(yourcolumn2) yourcolumn2... from table_name
0
 

Author Comment

by:theartha
ID: 35461485
@for_yan:
I got this error message:
'trim' is not a recognized built-in function name
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 41

Accepted Solution

by:
ralmada earned 1000 total points
ID: 35461520
you should use

ltrim(rtrim(yourcolumn))
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35461532
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
 
LVL 9

Assisted Solution

by:radcaesar
radcaesar earned 1000 total points
ID: 35461537
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
 
LVL 47

Expert Comment

by:for_yan
ID: 35461571

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
 
LVL 41

Expert Comment

by:ralmada
ID: 35461602
@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
 
LVL 47

Expert Comment

by:for_yan
ID: 35461620
I see...
Strange, that all those folks on internet are mentioning it.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 35461632
the link that you're mentioning seems to be a Coldfusion forum, Probably in Coldfusion there's a trim function.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35461669
Yes, I see, so RTRIM(LTRIM(...)) is the way to go.
So, after all I'm right that I stick to Oracle :)
 
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month16 days, 10 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question