Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19

Thread: Carriage Returns and Line Feeds

  1. #16
    Join Date
    Mar 2003


    I cant open it.Can you directly post it.You dont have to give me the whole data.I just need to address column...

  2. #17
    Join Date
    May 2003
    It will not allow me to post an mdb.

    And because of the carriage returns a txt (csv or tab) file will not make any sense.

  3. #18
    Join Date
    Feb 2003
    Johannesburg, South Africa

    Re: Carriage Returns and Line Feeds

    Originally posted by icculus100

    We are running MS SQL Server 2000 as the backend for our ERP system. The customer database stores the customer address (ie. 1,2&3) as a single record with carriage returns and linefeeds, rather than breaking it into separate records. I am trying to write a query that will break the address field into a field for each line (at the carriage return or linefeed) so that we can link the UPS shipping computer directly to a OBDC query view of our sales orders.

    The record currently looks something like:

    Attn. Purchasing
    Biggy Corporation
    123 Some Street

    I want it to look like:
    Attn. Purchasing
    Biggy Corporation
    123 Some Street

    If someone could give me a few pointers or examples to work with on this, It would really save my neck.

    Thanks A lot

    I don't know if you people have any knowledge of Perl, but here is a solution:

    $adr1 = "Attn. Purchasing\nBiggy Corporation\n123 Some Street";
    $adr1 =~ s/\r//g;
    @fields = split( /\n/, $adr1 );
    $counter = 1;
    foreach $field ( @fields ) {
    	print "address" . $counter . " > $field\n";
    Output :

    $ perl 
    address1 > Attn. Purchasing
    address2 > Biggy Corporation
    address3 > 123 Some Street
    You would tackle the broader problem with a SQL SELECT to get all the address, load the address ( with the key ) into an array, then loop through the array to insert the split parts in the new fields.

    You can modify this to fit just about any programming language.

    My 2c

    Last edited by nicc777; 05-14-2003 at 12:28 PM.

  4. #19
    Join Date
    May 2003
    Hi Everybody,

    I figured out the problem.

    The code that Claire gave me (Thanks Claire!) didn't like it when there wasn't a char(13) (some addresses only had one address line)

    I was able to do it with three select statements and unions.

    I had to fool around with where the charindex began looking for the next line to get it so that address 2 and 3 would not already have a carriage return at the beginning.

    attached is the code.

    Thanks to everyone who helped out!
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts