We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

truncate white spaces

Medium Priority
419 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.
Comment
Watch Question

Awarded 2011
Awarded 2011

Commented:
I think this should work  
trim(yourcolumn)
but it will keep white spaces inside the column

Awarded 2011
Awarded 2011

Commented:
select trim(yourcolumn1) yourcolumn1, trim(yourcolumn2) yourcolumn2... from table_name

Author

Commented:
@for_yan:
I got this error message:
'trim' is not a recognized built-in function name
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Awarded 2011
Awarded 2011

Commented:
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
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Awarded 2011
Awarded 2011

Commented:

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)

Commented:
@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
Awarded 2011
Awarded 2011

Commented:
I see...
Strange, that all those folks on internet are mentioning it.

Commented:
the link that you're mentioning seems to be a Coldfusion forum, Probably in Coldfusion there's a trim function.
Awarded 2011
Awarded 2011

Commented:
Yes, I see, so RTRIM(LTRIM(...)) is the way to go.
So, after all I'm right that I stick to Oracle :)
 
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.