查询效果图如下:
这个查询用于从地址字段 address_detail
中提取省、市、县(区)、以及详细地址信息,并根据地址中是否包含“区”或“县”进行不同的拆分处理。
-
省(province)提取:
SUBSTRING(address_detail, 1, LOCATE('省', address_detail)) AS province
- 使用
LOCATE('省', address_detail)
找到地址中省的位置。 SUBSTRING
函数从地址的开头提取到第一个“省”之前的部分,作为省的名称。
- 使用
-
市(city)提取:
SUBSTRING(address_detail, LOCATE('省', address_detail) + 1, LOCATE('市', address_detail) - LOCATE('省', address_detail) ) AS city
LOCATE('省', address_detail) + 1
找到省之后的位置。LOCATE('市', address_detail) - LOCATE('省', address_detail) - 1
计算省和市之间的字符数,提取市的名称。
-
县(区)提取:
CASE WHEN address_detail LIKE '%区%' THEN SUBSTRING(address_detail, LOCATE('市', address_detail) + 1, LOCATE('区', address_detail) - LOCATE('市', address_detail) ) WHEN address_detail LIKE '%县%' THEN SUBSTRING(address_detail, LOCATE('市', address_detail) + 1, LOCATE('县', address_detail) - LOCATE('市', address_detail) ) ELSE NULL END AS county
- 使用
CASE
表达式根据地址中是否包含“区”或“县”来判断。 - 如果包含“区”,则提取从市之后到区之前的部分作为县(区)的名称。
- 如果包含“县”,则提取从市之后到县之前的部分作为县(区)的名称。
- 使用
-
详细地址(addressDetail)提取:
CASE WHEN address_detail LIKE '%区%' THEN SUBSTRING(address_detail, LOCATE('区', address_detail) + 1) WHEN address_detail LIKE '%县%' THEN SUBSTRING(address_detail, LOCATE('县', address_detail) + 1) ELSE NULL END AS addressDetail
- 使用
CASE
表达式根据地址中是否包含“区”或“县”来判断。 - 如果包含“区”,则提取从区之后的部分作为详细地址。
- 如果包含“县”,则提取从县之后的部分作为详细地址。
- 使用
-
WHERE 子句:
-
WHERE address_detail LIKE '%省%' AND address_detail LIKE '%市%' AND (address_detail LIKE '%区%' OR address_detail LIKE '%县%');
- 确保地址中包含省和市信息。
- 只选择包含“区”或“县”的地址,以确保有县(区)信息可提取。
这样,整个查询就能有效地从地址字段中提取省、市、县(区)、以及详细地址信息,并根据不同情况进行相应的拆分处理。
整合代码如下:
SELECT
SUBSTRING(address_detail, 1, LOCATE('省', address_detail)) AS province,
SUBSTRING(address_detail, LOCATE('省', address_detail) + 1, LOCATE('市', address_detail) - LOCATE('省', address_detail)) AS city,
CASE
WHEN address_detail LIKE '%区%' THEN
SUBSTRING(address_detail, LOCATE('市', address_detail) + 1, LOCATE('区', address_detail) - LOCATE('市', address_detail))
WHEN address_detail LIKE '%县%' THEN
SUBSTRING(address_detail, LOCATE('市', address_detail) + 1, LOCATE('县', address_detail) - LOCATE('市', address_detail) )
ELSE
NULL
END AS county,
CASE
WHEN address_detail LIKE '%区%' THEN
SUBSTRING(address_detail, LOCATE('区', address_detail) + 1)
WHEN address_detail LIKE '%县%' THEN
SUBSTRING(address_detail, LOCATE('县', address_detail) + 1)
ELSE
NULL
END AS addressDetail
FROM
f_address
WHERE
address_detail LIKE '%省%' AND
address_detail LIKE '%市%' AND
(address_detail LIKE '%区%' OR address_detail LIKE '%县%');