CODEWITHSUNDEEP
sqlsql-server-2008
HL8
HL8

Reputation: 1419

SQL Server 2008 - separating Address field

I have an address column that contains address, state and postcode. I would like to extract the address, suburb, state, and postcode into separate columns, how can a do this as the length of the address is variable, there is a ^ to separate the address and "other" details. The State can be 2 or 3 characters long and the postcode is always 4 characters long.

PostalAddress                            TO BE  Address   Suburb       State  Postcode
28 Smith Avenue^MOOROOLBARK VIC 3138^    28 Smith Avenue  MOOROOLBARK  VIC    3138
16 Farr Street^HEYFIELD VIC 3858^        16 Farr Street   HEYFIELD     VIC    3858
17 Terry Road^LOWER PLENTY VIC 3093^     17 Terry Road    LOWER PLENTY VIC    3093

Upvotes: 1

Views: 1814

Answers (2)

Chou Chee Chong
Chou Chee Chong

Reputation: 1

In my case it's just to get a five-numeric from a string as a postcode:

Below is my code:

Select SUBSTRING([Column or string],patindex('%[0-9][0-9][0-9][0-9][0-9]%',[Column or string]),5) AS 'Postcode'

Upvotes: 0

ig0774
ig0774

Reputation: 41287

String parsing in SQL is messy and tends to be brittle. I usually think it's best to do these sort of tasks outside of SQL altogether. That said, given the mini-spec above, it is possible to parse the data into the fields you want like so:

select 
  left(PostalAddress, charindex('^', PostalAddress) - 1) as street_address,
  left(second_part, len(second_part) - charindex(' ', reverse(second_part))) as suburb,
  right(second_part, charindex(' ', reverse(second_part))) as state,
  reverse(substring(reverse(PostalAddress), 2, 4)) as postal_code
from (
  select
    PostalAddress,
    rtrim(reverse(substring(reverse(PostalAddress), 6, len(PostalAddress) - charindex('^', PostalAddress) - 5))) as second_part
  from Addresses
) as t1

Note that you'll need so substitute your table name for what I've called addresses in the subquery above.

You can see this in action against your sample data here.

Upvotes: 5

Related Questions