Stackoverflow #8844796

0

Please login or register to vote for this query.

(click on this box to dismiss)

Philosophy Meta

Q&A about the site for those interested in the study of the fundamental nature of knowledge, reality, and existence

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
)
select *
from (
  select
    row_number() over (order by PostalAddress) as row_number,
    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 t
  ) as t1
) as t2
where row_number between 1 and 3

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results