MySQL Workbench中如何移除City字段末尾附带的2位州缩写

原City字段截取逻辑的长度计算基准错误：你使用length(OwnerAddress)-2作为截取长度时，参考的是完整地址字符串的总长度，而非你提前取出的「城市+州」子串长度，最终截取长度远大于城市段本身长度，因此末尾的2位州缩写没有被移除。
另外原Street字段的截取起始位没有跳过门牌号后的第一个空格，会导致街道名开头携带多余空格。

利用SUBSTRING_INDEX按空格分割取段的特性实现拆分，不需要手动计算字符串长度，兼容性更强，逻辑更简单：

• StreetNumber：直接取第一个空格前的内容即可，原逻辑的嵌套写法可以简化
• Street：去掉开头的门牌号、末尾的「城市+州」段，剩余中间内容做去空格处理
• City：先取地址最后2个空格分割出的「城市+州」段，再取该段第一个空格前的内容，就是纯城市名
• State：原逻辑正确，直接取最后一个空格后的内容即可

SELECT
  SUBSTRING_INDEX(OwnerAddress, ' ', 1) AS StreetNumber,
  TRIM(SUBSTRING(
    OwnerAddress,
    LOCATE(' ', OwnerAddress) + 1,
    LENGTH(OwnerAddress) 
    - LENGTH(SUBSTRING_INDEX(OwnerAddress, ' ', 1)) 
    - LENGTH(SUBSTRING_INDEX(OwnerAddress, ' ', -2))
    - 2
  )) AS Street,
  SUBSTRING_INDEX(SUBSTRING_INDEX(OwnerAddress, ' ', -2), ' ', 1) AS City,
  SUBSTRING_INDEX(OwnerAddress, ' ', -1) AS State
FROM nashhousing;

注：如果城市名本身包含空格（比如NEW YORK NY这类多词城市名），这个逻辑同样适用，因为规则固定取最后一位为州、倒数第二位为城市，不会把城市名里的空格当成分割边界切错。

内容的提问来源于stack exchange，提问作者Marcella