sql server trim whole database

Is there a way to trim beginning and trailing spaces, tabs, and new lines from a whole db?
jackjohnson44Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
 
Rick_RickardsCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
jackjohnson44Author Commented:
this did not work
select replace(replace(testcasename, char(13), ""), char(10), "")

It says
An object or column name is missing or empty
0
 
Rey Obrero (Capricorn1)Commented:
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
 
Rick_RickardsCommented:
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
 
ursangelCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.