sql - 仅从 sql 中的地址获取邮政编码

Question

从 ODLN 中选择地址

这是我的结果：

15 Changi North Way Level 2 SSG WH Singapore 498770 (8:30am ~ 5pm; Avoid 11:30am ~ 1:30pm, send before 4pm)
15 Changi North Way Level 2 SSG WH Singapore 498770 (8:30am ~ 5pm; Avoid 11:30am ~ 1:30pm, send before 4pm)
15 Changi North Way Level 2 SSG WH Singapore 498770 (8:30am ~ 5pm; Avoid 11:30am ~ 1:30pm, send before 4pm)
200 Victoria Street #03-09  Bugis Junction  Singapore 188021 Singapore
348 Jalan Boon Lay (Lunch: 12pm ~ 1pm) Singapore 619529
46 Penjuru Lane, Level 4 (Office), C&P Hub 3 Singapore 609206 (Avoid Lunch Time: 11.30am to 1pm, deliver before 4pm)
46 Penjuru Lane, Level 4 (Office), C&P Hub 3 Singapore 609206 (Avoid Lunch Time: 11.30am to 1pm, deliver before 4pm)

我需要我的结果：

498770 
498770
498770  
188021
619529
609206
609206

标签： sqlsql-server-2016

Answer 1

如果我们假设 6 位邮政编码总是出现在 text 之后Singapore，那么我们可以在这里使用基本字符串函数：

SELECT SUBSTRING(address, CHARINDEX('Singapore', address) + 10, 6) AS zip
FROM yourTable;

演示