Stripping newlines and spaces from a field and splitting it to another

Also Known As: Recovering from some wingnut's idea of acceptable d

Page 1 of 1

3 Replies - 744 Views - Last Post: 10 March 2008 - 02:50 PM Rate Topic: -----

#1 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Stripping newlines and spaces from a field and splitting it to another

Posted 07 March 2008 - 10:38 PM

Greetings all,

I've inherited a database and application created by some wingnut who needs to go read a book on data normalization, and I'm not quite sure of the easiest way to fix one part of it.

There is a table called "customers". It has fields for addresses. Among these fields are address and address_2. (address_2 is for the second address line.)

Unfortunately, when the front end was created the programmer failed to notice the field for second address line. He saw the spec that called for this field in the interface, but either missed it in the database or elected to not use it because he was, ostensibly, way to smart for that.

Instead, he made it so that whenever a customer entered a second address line, the program appended an apparently random and pointless number of spaces to the first address line, added a \n (newline) after those, and appended the second address line after that and wrote the resulting string to the first address line field in the database.

The good news is that I can easily find all the affected rows with something like: select address from customers where address like "%\n%";

The bad news is that I need to figure out how to alter those records thusly: For all records 'where address like "%\n%"', copy the value after the \n into the field 'address_2', and strip that value from 'address' along with the \n and the now trailing spaces.

And I haven't a clue how to do something like that.

Any ideas?

Thanks,
Wayne

Is This A Good Question/Topic? 0
  • +

Replies To: Stripping newlines and spaces from a field and splitting it to another

#2 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5940
  • View blog
  • Posts: 12,868
  • Joined: 16-October 07

Re: Stripping newlines and spaces from a field and splitting it to another

Posted 08 March 2008 - 05:22 AM

This is pretty database specific. If your database engine has two functions, an instring to find the pattern and a substring to cut it up, then you can just parse stuff in the database. If not, consider dumping it out and playing with a nice text editor. I like to translate newlines to "~" for external processing, because no one can reasonably use a tilde in normal text. Then, if you export the data, it's all on the same line.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#3 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Stripping newlines and spaces from a field and splitting it to another

Posted 08 March 2008 - 01:34 PM

View Postbaavgai, on 8 Mar, 2008 - 06:22 AM, said:

This is pretty database specific. If your database engine has two functions, an instring to find the pattern and a substring to cut it up, then you can just parse stuff in the database. If not, consider dumping it out and playing with a nice text editor. I like to translate newlines to "~" for external processing, because no one can reasonably use a tilde in normal text. Then, if you export the data, it's all on the same line.

Hope this helps.


Heh - I can't believe I forgot to mention what database we're using. I got too caught up in describing the problem to think about the platform.

It's mySql 4.1.2.

There are tens of thousands of records, so I'd much prefer somehow doing this is in some sort of mySql command to exporting, modifying, and importing - just seems less error-prone. Though, that is probably a good last resort.

Any way to do this within mySql?

Thanks,
w
Was This Post Helpful? 0
  • +
  • -

#4 wzeller  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 78
  • Joined: 09-April 06

Re: Stripping newlines and spaces from a field and splitting it to another

Posted 10 March 2008 - 02:50 PM

In case anybody finds this thread when looking for a solution to a similar problem, here's the answer I was able to obtain from another place. (This is my favorite forum for this kind of thing, so I figured I'd contribute it here even though it came from elsewhere...)


UPDATE customer
   SET address_2 = SUBSTRING(address, INSTR(address, '\n') + 1) 
 WHERE INSTR(address, '\n') > 0;

UPDATE customer
   SET address = TRIM(TRAILING ' ' FROM SUBSTRING(address, 1, INSTR(address, '\n') - 1)) 
 WHERE INSTR(address, '\n') > 0;
(After adding the address_2 field to the table if it isn't already there.)

Thanks,
w
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1