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

SQL Server remove trailing spaces in table fields

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
dodgerfan
Asked:
dodgerfan
1 Solution
 
Patrick TallaricoFSEP Systems AnalystCommented:
can you update the fields in the table using rtrim?

Update table
set column = rtrim(column),
     column2=rtrim(column2)
0
 
dqmqCommented:
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
 
tliottaCommented:
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Patrick TallaricoFSEP Systems AnalystCommented:
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
 
tliottaCommented:
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
 
Patrick TallaricoFSEP Systems AnalystCommented:
@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
 
dodgerfanAuthor Commented:
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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