SQL中的字符串解析很杂乱，并且往往很脆弱。我通常认为最好完全在SQL之外执行这些任务。就是说，根据上述迷你规范，可以将数据解析为所需的字段，如下所示：

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

请注意，您将需要用表名替换上面的子查询中我所谓的地址。