Solved

SQL Server remove trailing spaces in table fields

Posted on 2012-04-04
7
452 Views
Last Modified: 2012-04-05
Is there a way to remove the trailing spaces in fields in a sql server table? I don't mean using rtrim or something like that for viewing. I want to permanently remove any trailing white space in the fields of various tables. I have a lot of records and fields, so doing it one record at a time manually is not possible. I'm trying to find out if it is possible. I'm using SQL Server 2005. Thanks.
0
Comment
Question by:dodgerfan
7 Comments
 
LVL 6

Accepted Solution

by:
Patrick Tallarico earned 500 total points
ID: 37808625
can you update the fields in the table using rtrim?

Update table
set column = rtrim(column),
     column2=rtrim(column2)
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37808632
update yourtable
   set
     yourcolumn1 = rtrim(yourcolumn1)
  ,  yourcolumn2 = rtrim(yourcolumn2)


The above could be automated across a table, database, or server, but it is probably easier to do just do it manually with copy/paste into a TSQL script.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37808717
If you want to exclude something like rtrim(), then I don't understand what you mean.

That is, I would use an UPDATE statement that set the value of a column to be the value of rtrim(<column>).

Of course, that assumes that the column is VCHAR in the first place. If not, then I'd alter the column data type first; then I'd UPDATE to set the values.

Can you clarify if you're excluding rtrim() or if it's just the use of rtrim() for retrieval/viewing?

Tom
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37808738
I could be wrong, but I think @dodgerfan was speaking only of the fact that when used in a View, rtrim, doesn't affect a change onto the actual table.  When used in an update statement, rtrim() should work just fine for @dodgerfan.

@dodgerfan, please let me know if I am off on this train of thought.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37809022
Yep, we probably see it the same way. But every once in a while, someone asks a question like this and actually has a surprising reason behind it. I usually learn something from those reasons. I've turned a little cautious just in case.

Tom
0
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37809093
@tliotta, I definitely agree.  I wasn't intending to downplay your comment.  In addition, it's a good comment to check the data type.  I often forget to ask that question.  Thanks for the reminder.
0
 

Author Comment

by:dodgerfan
ID: 37812238
I apologize for not getting back to this earlier, and for the confusion. I was speaking of using RTRIM in a view. I have no idea why I never thought of using it in an update statement. I've already tested it and it works great. Thanks for help.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delete from table 6 48
CREATE DATABASE ENCRYPTION KEY 1 80
Need help in debugging a UDF results 7 55
Using this function 4 42
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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