SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) 
 Jun 12 2020 20:39:00 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table addresstable (address varchar(100));
insert into addresstable values (('Marco Polo street 8a, 44000 Vienna'));
insert into addresstable values (('Marco Polo street 8a, 44000 Vienna Old Town'));
insert into addresstable values (('Marco Polo street 8a,44000 Vienna'));
insert into addresstable values (('Marco Polo street 8a, Marco Polo street 8b, 44 000 Vienna Old Town'));
insert into addresstable values (('Marco Polo street 8a, 44 00 0 Vienna'));
insert into addresstable values (('Marco Polo street 8a, IT-44000 Vienna'));
insert into addresstable values (('Marco Polo street 8a, Vienna 44000'));


select * from addresstable;


address


Marco Polo street 8a, 44000 Vienna

Marco Polo street 8a, 44000 Vienna Old Town

Marco Polo street 8a,44000 Vienna

Marco Polo street 8a, Marco Polo street 8b, 44 000 Vienna Old Town

Marco Polo street 8a, 44 00 0 Vienna

Marco Polo street 8a, IT-44000 Vienna

Marco Polo street 8a, Vienna 44000


-- your original query
select SUBSTRING(SUBSTRING(address,CHARINDEX(',',address,0)+1,LEN(address)),0,CHARINDEX(' ',address,0)+1) as postcode,
       SUBSTRING(SUBSTRING(address,CHARINDEX(' ',address,0)+1,LEN(address)),CHARINDEX(',',address,0)+1,LEN(address)) as city
  from addresstable


postcode
city


 44000
 Vienna

 44000
 Vienna Old Town

44000
Vienna

 Marco
 Polo street 8b, 44 000 Vienna Old Town

 44 00
 0 Vienna

 IT-44
000 Vienna

 Vienn
a 44000


-- cte to get part after last comma
with tmp as (
select TRIM(REVERSE(SUBSTRING(REVERSE(address),0,CHARINDEX(',',REVERSE(address))))) lastpart
  from addresstable
  ) 
  select * from tmp


lastpart


44000 Vienna

44000 Vienna Old Town

44000 Vienna

44 000 Vienna Old Town

44 00 0 Vienna

IT-44000 Vienna

Vienna 44000


-- solution using PATINDEX to get all numbers and spaces from the left, will break if postcode has alpha characters 
with tmp as (
select TRIM(REVERSE(SUBSTRING(REVERSE(address),0,CHARINDEX(',',REVERSE(address))))) lastpart
  from addresstable
  ) 
  select LEFT(lastpart, PATINDEX('%[^0-9 ]%', lastpart) -1) as postcode,
         RIGHT(lastpart, len(lastpart) - PATINDEX('%[^0-9 ]%', lastpart) + 1) as city
  from tmp


postcode
city


44000
Vienna

44000
Vienna Old Town

44000
Vienna

44 000
Vienna Old Town

44 00 0
Vienna


IT-44000 Vienna


Vienna 44000


-- solution using substring like your's before, will break if postcode has spaces within
with tmp as (
select TRIM(REVERSE(SUBSTRING(REVERSE(address),0,CHARINDEX(',',REVERSE(address))))) lastpart
  from addresstable
  ) 
  select SUBSTRING(lastpart,0,CHARINDEX(' ',lastpart,0)+1) as postcode,
         SUBSTRING(lastpart,CHARINDEX(' ',lastpart,0)+1,LEN(lastpart)) as city
  from tmp


postcode
city


44000
Vienna

44000
Vienna Old Town

44000
Vienna

44
000 Vienna Old Town

44
00 0 Vienna

IT-44000
Vienna

Vienna
44000


-- solution using PATINDEX with modifications to have postcode as int result 
with tmp as (
select TRIM(REVERSE(SUBSTRING(REVERSE(address),0,CHARINDEX(',',REVERSE(address))))) lastpart
  from addresstable
  ) 
  select try_convert(int, nullif(replace(LEFT(lastpart, PATINDEX('%[^0-9 ]%', lastpart) -1), ' ', ''),'')) as postcode,
         RIGHT(lastpart, len(lastpart) - PATINDEX('%[^0-9 ]%', lastpart) + 1) as city
  from tmp


postcode
city


44000
Vienna

44000
Vienna Old Town

44000
Vienna

44000
Vienna Old Town

44000
Vienna

null
IT-44000 Vienna

null
Vienna 44000
postcode	city
44000	Vienna
44000	Vienna Old Town
44000	Vienna
Marco	Polo street 8b, 44 000 Vienna Old Town
44 00	0 Vienna
IT-44	000 Vienna
Vienn	a 44000