Okay, I've got some data that, due to our vendor, isn't exactly neatly packaged when we insert it into our database. . .here's a (modified) excerpt:

ACCT_TEXT1ACCT_TEXT2ACCT_TEXT3ACCT_TEXT4ACCT_TEXT5ACCT_TEXT6
BIGGLE EQUITY MGMT ASSOC. PIGGLE BIGGLING PLAN FUTURES I & II TWO LONDDDD CENTER TRENTON NJ 97302-5115
AGRICULTURAL LIFE INSURANCE CO MAIN 111 ELEVEN AVE PO BOX 123 MORRISTOWN NJ 97631-0633
MILLENA BANC MILLINER BANK & TRUST CO 456 THAUMATURGE AVENUE NEWSOME MA 07145-6316
BRICKABRACK ADVISORS LIBERTARIAN BANK 888 CRAZED CREEK ROAD STE 11 MEMPHIS KY 77882-5394
FOO COMMUNITY BANCORP THE FOOVILLE SAVINGS BANK 18 FOOL AVENUE FOO NY 10567-1735
ELROND ASSET MANAGEMENT BRA3255 CSHYTFD ATTN SETTLEMENTS DEPT. 466 LURCHING AVENUE NEW YORK NY 10347
CRIPPLE CREEK INVESTMENT ADVISORS GLIB TALKER 666 9TH AVENUE 8TH FLOOR NEW YORK NY 10235-2302
SEPARATIST STATE BANK ATTN: JOSE KWAN XYZ FINANCIAL CENTER 145 TALKING FAST ROAD NEWBURGH NY 07083-2340


Essentially, I need to separate this out into recognizable name/address relations, complete with city/state/postal code divided into identifiable columns.

My first inclination would be to use a regex of some kind, but I'm fairly certain Merlyn would warn (correctly, I think) that this is one of those verboten topics for regexes. About the only thing I can be relatively sure of is that city/state/zip will be last (though not always in the same column).

Any advice appreciated. Even "avoid doing this like the plague".

In reply to Data Salad Address Problem by SamCG

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.