Solved

SQL Server remove trailing spaces in table fields

Posted on 2012-04-04
7
450 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Testing connection to sql 7 60
PERFORMANCE OF SQL QUERY 13 73
Sql Permission 6 61
Strange msg in the SSMS pane 13 56
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

832 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