Q&A for community managers, administrators, and moderators
with t as ( select '28 Smith Avenue^MOOROOLBARK VIC 3138^' as str union select '16 Farr Street^HEYFIELD VIC 3858^' as str union select '17 Terry Road^LOWER PLENTY VIC 3093^' as str ), t1 as ( select str, left(str, charindex('^', str) - 1) as street_address, reverse(substring(reverse(str), 2, 4)) as postal_code, reverse(substring(reverse(str), 6, len(str) - charindex('^', str) - 6)) as second_part from t ) select street_address, postal_code, second_part, charindex(' ', reverse(second_part)) from t1