0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

with t as (
  select '28 Smith Avenue^MOOROOLBARK VIC 3138^' as PostalAddress
  union
  select '16 Farr Street^HEYFIELD VIC 3858^' as PostalAddress
  union
  select '17 Terry Road^LOWER PLENTY VIC 3093^' as PostalAddress
), t1 as (
  select
    PostalAddress,
    rtrim(reverse(substring(reverse(PostalAddress), 6, len(PostalAddress) - charindex('^', PostalAddress) - 5))) as second_part
  from t
)
select 
  left(PostalAddress, charindex('^', PostalAddress) - 1) as street_address,
  left(second_part, len(second_part) - charindex(' ', reverse(second_part))) as city,
  right(second_part, charindex(' ', reverse(second_part))) as state,
  reverse(substring(reverse(PostalAddress), 2, 4)) as postal_code
from t1

fork query

edited jan 13 12

created jan 13 12 anonymous