Post reply

Display address in vertical format

  • July 31, 2009 at 4:14 am

    #73885

    Morning,

    I have a single attribute which shows the address as The Street, Any Town, County, Post Code

  • July 31, 2009 at 4:17 am

    #1032841

    Now for the question! ;-(

    I want to show the address as

    The Street

    Any Town

    County

    Post Code

    I have seen this done by using COALESCE, but cannot find the code anywhere. Has anybody got a better filing sustem than me? Thanks

    Colin

  • July 31, 2009 at 7:50 pm

    #1033235

    What do you mean by a single "attribute"? Do you mean it's all a single string to begin with?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 31, 2009 at 9:35 pm

    #1033266

    Hi Jeff,

    I think, need the statement for the comma delimited row to the column,

    Colin already use the COALESCE statement, actually may Colin need the better one then COALESCE!!!

  • July 31, 2009 at 10:59 pm

    #1033278

    am i missing something? it looks like a comma delimited string, so you could replace the comma with CrLf (Char(13) + CHAR(10) ) for display purposes, or did you want each item on a separate row of data(so you'd use a Split() function?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 31, 2009 at 11:52 pm

    #1033279

    arun.sas (7/31/2009)

    Hi Jeff,

    I think, need the statement for the comma delimited row to the column,

    Colin already use the COALESCE statement, actually may Colin need the better one then COALESCE!!!

    It looks like and could be a comma delimited string but I need to be sure. It could just as easily be a bunch of columns where Colin used commas just to separate them on the first post.

    Colin, what say yea?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 4, 2009 at 7:06 am

    #1034217

    Aplogies for delay.

    It is indeed a comma delimited string. Which I am taking from a view which combines data in 2 tables, in different formats. Don't ask! 😉

    I want to=o be able to select the string in a user friendly way, to use as an address in a letter. Mention of replacing the commas with an end of line character sounds interesting and I would welcome more information on that.

    Thank you

    Colin

  • August 4, 2009 at 7:53 am

    #1034248

    the REPLACE function is pretty straight forward;

    here's an example:

    with myAddresses As (

    SELECT '164 N. Daves Street,Madisonville,FL 42431' AS ADDR1 UNION ALL

    SELECT '1333 Weller Ave,Miami,FL 40208' AS ADDR1 UNION ALL

    SELECT '1011 Mary St,Fort Lauderdale,FL 40204' AS ADDR1

    )

    select REPLACE(ADDR1,',',CHAR(13) + CHAR(10) )

    FROM myAddresses

    --Results:

    /*

    164 N. Daves Street

    Madisonville

    FL 42431

    1333 Weller Ave

    Miami

    FL 40208

    1011 Mary St

    Fort Lauderdale

    FL 40204

    */

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply