Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Carriage Returns and Line Feeds

  1. #1
    Join Date
    May 2003
    Posts
    10

    Carriage Returns and Line Feeds

    Hello,

    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:

    Address:
    Attn. Purchasing
    Biggy Corporation
    123 Some Street

    I want it to look like:
    Address1:
    Attn. Purchasing
    Address2:
    Biggy Corporation
    Address3:
    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    create table address (ad varchar(100))
    go
    insert into address select 'Attn. Purchasing'
    insert into address select 'Biggy Corporation'
    insert into address select '123 Some Street'
    go
    alter table address add id int identity(1,1)
    go
    select 'Address'+convert(varchar,[id])+':'+char(13)+char(10)+ad+char(13)+char(10) from address
    go
    alter table address drop column id
    go

  3. #3
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    --1.Create table with adds column
    create table a(adds varchar(100))

    --2.Insert value as you described
    insert into a select 'Attn. Purchasing
    Biggy Corporation
    123 Some Street'

    --3.Seperate it to 3 lines
    declare @no1 int
    set @no1= (select charindex(char(13),adds) from a)
    declare @no2 int
    set @no2 =(select charindexchar13),adds,charindex(char(13),adds)+1) from a)

    select rtrim(left(adds,@no1-1)) as [address1:],rtrim(ltrim(substring(adds,@no1+1,@no2-@no1))) as [address2:],ltrim(rtrim(right(adds,len(adds)-@no2-1))) as [address3:] from a

  4. #4
    Join Date
    May 2003
    Posts
    10
    Mak,

    Close but no cigar ...


    I guess I wasn't clear enough.

    Currently, three lines of an address is stored in one record, with carriage returns and line feeds.

    I need to break these into three fields: Address1, Address2, Address3

    So rather than....

    RecNo Add
    1 Purchasing
    Biggy Corp
    123 Some St


    ...it would be....
    RecNo Add1 Add2 Add3
    1 Purchasing Biggy Corp 123 Some St



    thanks.

  5. #5
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Did you try my method?

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  7. #7
    Join Date
    May 2003
    Posts
    10
    Claire,

    Thanks that has helped me quite a bit...

    Your code snipit works for 1 record, but not for more than 1 since your select queries on the declarations return more than 1 value.

    I think i can work it through and maybe figure it out... if you happen to know something off hand... it would be much appreciated. Thanks for all your help so far.

  8. #8
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Remove the declaration!And run only the following select statement!

    select rtrim(left(adds,charindex(char(13),adds)-1)) as [address1:],
    rtrim(ltrim(substring(adds,charindex char(13),adds)+1,charindex(char13),adds,charindex( char(13),adds)+1)-charindex(char(13),adds)))) as [address2:],
    ltrim(rtrim(right(adds,len(adds)-charindex(char(13),adds,charindex(char(13),adds)+1 )-1))) as [address3:] from a

  9. #9
    Join Date
    May 2003
    Posts
    10
    Hey Claire Thanks again..

    I haven't quite gotten it...

    I'm closer though...

    I'm getting this error:

    Server: Msg 536, Level 16, State 3, Line 1
    Invalid length parameter passed to the substring function.

    I'm digging through it to find where it is.

    Thanks a lot.

  10. #10
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Please download from attachment.I cant post direcly,the web form modified my code wrongly if I directly post it.
    Attached Files Attached Files

  11. #11
    Join Date
    May 2003
    Posts
    10
    thanks.. i'll try that
    Last edited by icculus100; 05-12-2003 at 03:28 PM.

  12. #12
    Join Date
    May 2003
    Posts
    10
    still getting same error.


    If I take out the "+1" on the third charindex on the second address, it works but doesn't break addresses apart correctly.

    Thanks for everything so far.

  13. #13
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    I dont know why you are getting such error.Maybe your table insert is not supported by using char(13) to delimite it.
    Can you show me some address input you have in your table(maybe 10 rows is fine)

    According to your real address data,we can see where is the problem from?

  14. #14
    Join Date
    May 2003
    Posts
    10
    Attached is an MDB with sample data

    (I am using MS SQL Server though)

  15. #15
    Join Date
    May 2003
    Posts
    10
    Attachment....
    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
  •