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 |