有了这个表，我试图删除在邮政编码和城市中出现的部分地址。

+----------------------------------------------+----------+------------+
| address                                      | zip_code | city       |
+----------------------------------------------+----------+------------+
| Oceans Group, 12 Pear Tree Road, Derby       | DE23 6PY | Derby      |
| 970 Stockport Road                           | M19 3NN  | Manchester |
| Cartridge World Guiseley                     |          | Edinburgh  |
| 33-41 Kelvin Avenue                          | G52 4LT  | Glasgow    |
| Cartridge World Haymarket, 54 Dalry Road, UK | EH5 1HX  | Edinburgh  |
| 50 Otley Road, Leeds, LS20 8AH, UK           | LS20 8AH |            |
+----------------------------------------------+----------+------------+

像这样的

SUBSTR('Oceans Group, 12 Pear Tree Road, Derby', 'DE23 6PY', 'Derby') returns 'Oceans Group, 12 Pear Tree Road, '
SUBSTR('50 Otley Road, Leeds, LS20 8AH, UK', 'LS20 8AH', '') returns '50 Otley Road, Leeds, , UK'

希望这段代码能为您节省一些时间。

CREATE TABLE address_table(
      address    STRING
    , zip_code   STRING
    , city       STRING
);

INSERT INTO address_table VALUES ("Oceans Group, 12 Pear Tree Road, Derby", "DE23 6PY", "Derby");
INSERT INTO address_table VALUES ("970 Stockport Road", "M19 3NN", "Manchester");
INSERT INTO address_table VALUES ("Cartridge World Guiseley", "", "Edinburgh");
INSERT INTO address_table VALUES ("33-41 Kelvin Avenue", "G52 4LT", "Glasgow");
INSERT INTO address_table VALUES ("Cartridge World Haymarket, 54 Dalry Road, UK", "EH5 1HX", "Edinburgh");
INSERT INTO address_table VALUES ("50 Otley Road, Leeds, LS20 8AH, UK", "LS20 8AH", "");