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

Answer 1

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'

Answer 2

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