carrage return character withn column value

[fikir]

hello every one

I have a table

tbl1

perId perName perAddress
1 James washington DC, fairfax VA
2 Bob seattle WA, Los Angeles CA


I want to write a query so that the different addresses will be carrage return separated

the result should look like


perId perName perAddress
1 James washington DC
fairfax VA
2 Bob seattle WA
Los Angeles CA

Thanks

 

[SQLSister]

You should not be storing multiple addresses in the same field.

You need to normalize your data.

however if you must, I would look at the replace function and replace that comma with the ascii code for a carriage return. Or possibly you may need carraige return line feed to get it on another line. This of course assumes that your data only has commas between the multiple addresses and not within them, which is a pretty big assumption.

"NOTHING is more important in a database than integrity." ESquared

 

[fikir]

Thank you sqlSister

 

[gmmastros]

Also, you should be made aware that when you look at the data in Query Analyzer, you may not see your carriage return.

In grid view, you will not see them. If you change the view to text, then you will see the carriage returns.

To view the data in text...

Click Tools -> Option
Click 'Results' tab.
Change 'Default Results Target' to 'Results in text'

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

Weighing in with SQLSister:

You should not be storing multiple addresses in the same field.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[fikir]

Thank you every one that was a great help