[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

sql server trim whole database

Posted on 2007-11-29
7
Medium Priority
?
981 Views
Last Modified: 2012-06-22
Is there a way to trim beginning and trailing spaces, tabs, and new lines from a whole db?
0
Comment
Question by:jackjohnson44
[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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 20375601
you can do that only per table, and per table, you have to specify all the columns where you want to perform that operation.

the triming can be done with the function TRIM(), and the newline would be removed using the REPLACE() funciton:

UPDATE yourtable
  SET yourfield = trim(replace(replace(yourfield, char(13), ""), char(10), ""))
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 20375607
to trim leading and trailing spaces,just use
Trim(string)
or trim([fieldName])

to remove new lies and carriage return use Replace function

Repalce(Replace(xstring,chr(10),""),chr(13),"")
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20375638
Easiest way I can think of off hand would be to write a simple function to search for and trim said characters and then....
Link all tables to an Access Database via an ODBC connection then...
Cycle thru all of the linked tables, fields and records and trim all the fields found in every record from every table.  

Depending on how much data you're talking about it may take some time but once started the process could be left to simply run till it is done.  To speed things up it would probably make sense for the function to consider only text and memo fields in the process (why fiddle with that that need not be reviewed).  

Would that solution be acceptable for you?

Rick
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jackjohnson44
ID: 20375667
this did not work
select replace(replace(testcasename, char(13), ""), char(10), "")

It says
An object or column name is missing or empty
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20375867
select replace(replace(testcasename, char(13), ""), char(10), "") from nameoftable

change { testcasename } withe name of the field from the table
change { nameoftable } with the actual name of the table
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20378817
Hey Jack,

Are you willing to let Access resolve this for you?  You'll need an ODBC connection, (not hard to do) and a copy of Access and a little code that I can help you with.  After that it's just a matter of linking all your tables, running the code and waiting for it to finish.  The elegance of this method is that it can easily sweep thru ever record, text field and table for you.  Admittedly I'm Access biased so if anyone can think of a way to get SQL server to do this I'm as eager as anyone to see it done, but absent any suggestions I know I can get Access to do what you're wanting done.

Rick
0
 
LVL 5

Expert Comment

by:ursangel
ID: 20380936
For trialing and beginning spaces you can use LTRIm()  and RTrim () functions and for tabs and new lines, you need to use the replace methods. But everything need to be done till the table level
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

650 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