Display address in vertical format

Question

Post reply

Display address in vertical format

Colin Betteley

Hall of Fame

Points: 3095
More actions
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

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

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

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

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)

arun.sas SSChampion Points: 11831 More actions · Answer 3

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!!!

Lowell SSC Guru Points: 323518 More actions · Answer 4

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!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

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)

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 6

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

Lowell SSC Guru Points: 323518 More actions · Answer 7

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!