Separating Address Column (Carriage Return)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Separating Address Column (Carriage Return)

Author

Topic

Jameses
Starting Member

1 Post

Posted - 2014-09-18 : 06:43:51

Good morning, this is my first post so I hope its in the correct place. I have a address column in one of my databases that I need to split out by carriadge return but don't know what to start

This is my example query

SELECT
Address
FROM
PUB."Address"
Where "Id" = 123456

Results

3 The StreetMayfairLondonEngland

I would like the results to show

Either

3 The Street
Mayfair
London
England

Or the same but horizontal

Can anyone help

Thanks

DJ

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-18 : 09:31:14

So, you have embedded CRs in your data? See this article about a splitter function and see if you can apply it to some of your data.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Your revised query might be something like:


SELECT
Address,
split.itemnumber,
split.item
FROM
PUB."Address"
cross apply DelimitedSplit8K(Address, char(13)) split
Where "Id" = 123456

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources