SQL Server 2019

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

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table tbl (address varchar(100));
 insert into tbl values 
 ('aaa-00000'),
 ('11111-bbb'),
 ('ccc-22222-bbb'),
 ('ddd-12345-eee-67890'),
 ('12345-23456-34567-7890');
5 rows affected

SELECT address,IIF((PatIndex('%[0-9][0-9][0-9][0-9][0-9]%', address)>0), reverse(substring(reverse(address), PatIndex('%[0-9][0-9][0-9][0-9][0-9]%', reverse(address)), 5)) , NULL) AS postalCode
from tbl


address
postalCode


aaa-00000
00000

11111-bbb
11111

ccc-22222-bbb
22222

ddd-12345-eee-67890
67890

12345-23456-34567-7890
34567

address	postalCode
aaa-00000	00000
11111-bbb	11111
ccc-22222-bbb	22222
ddd-12345-eee-67890	67890
12345-23456-34567-7890	34567