Solved

SQL Server remove trailing spaces in table fields

Posted on 2012-04-04
7
455 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

707 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