Split address string with carriage returns

Question

I have an address field where all the address details are held in one column, I want to create some labels so need to be able to split the address into the correct postal format.

Example of the data I have:

Address Column: 1 somehwere Road Town County Postcode

I would like to get the address in this format so the address is split into columns:

Address1 Column: 1 Somewhere Road

Address2 Column: Town

Address3 Column: County

Address4 Column: Postcode

Possible duplicate of [How do I split a string so I can access item x?](https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — tom redfern, May 07 '18 at 08:58

score 2 · Accepted Answer · answered Jul 29 '14 at 09:08

Think ive figured it out, I need to use the below sort of query to find the carriage return and then use left and right queries to find the part of the address I want, this is the first part and gives me the road details out of the address

left(Address, (CHARINDEX(CHAR(13) + CHAR(10), Address, CHARINDEX(CHAR(13) + CHAR(10), Address)))) as 'Address1'

score -2 · Answer 2 · answered Jul 29 '14 at 08:54

-2

You need to refer to procedure language included into RDBMS you use. There is no way to do this in pure SQL.

answered Jul 29 '14 at 08:54

fed.pavlo

251
1
9

https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x – tom redfern May 07 '18 at 08:58

Split address string with carriage returns

2 Answers2