MySql 查询特定州并从返回结果中分离出城市

Question

我的mysql表中有以下数据:

Spring Texas
Corpus Christi Texas
Orange California
Los Angeles California

数据的右侧始终是州名称。我想运行一个仅显示加利福尼亚州城市的 SQL 查询，并将 $city 设置为返回字符串的城市名称部分的数组。有些城市有洛杉矶等空间，有些则没有。

完成此操作的最佳方法是什么，以确保我只获得加利福尼亚州城市，并且仅分配给 $city 的城市部分

类似这样的东西，但是从 sql 的角度来看这是最好的方法吗？

Select
   terms.name
From
  terms
Where
  terms.name Like '%Texas%'

Answer 1

尝试标准化数据。

如果不能，请设置一个包含州名称的表。

SELECT c.col,
  TRIM(SUBSTR(c.col, 1, LENGTH(c.col) - LENGTH(c.state))) AS city,
  c.state AS state
FROM (SELECT a.col,
    CASE
      WHEN b.state IS NULL
        THEN SUBSTRING_INDEX(a.state, ' ', -1)
      ELSE b.state
    END AS state
  FROM
    (SELECT col,
      CASE
        WHEN LENGTH(col) - LENGTH(REPLACE(col, ' ', '')) + 1 = 2
          THEN SUBSTRING_INDEX(col, ' ', -1)
        WHEN LENGTH(col) - LENGTH(REPLACE(col, ' ', '')) + 1 > 2
          THEN SUBSTRING_INDEX(col, ' ', -2)
        ELSE NULL
      END AS state
    FROM bad_data) a
  LEFT JOIN state_names b ON b.state = a.state) c

结果

|                  COL |           CITY |      STATE |------------------------------------------------------|         Spring Texas |         Spring |      Texas || Corpus Christi Texas | Corpus Christi |      Texas ||    Orange California |         Orange | California ||    New York New York |       New York |   New York |

See the demo

If you can't set up a table, then this query should do it:

SELECT c.col,
  TRIM(SUBSTR(c.col, 1, LENGTH(c.col) - LENGTH(c.state))) AS city,
  c.state AS state
FROM (SELECT a.col,
    CASE
      WHEN b.state IS NULL
        THEN SUBSTRING_INDEX(a.state, ' ', -1)
      ELSE b.state
    END AS state
  FROM
    (SELECT col,
      CASE
        WHEN LENGTH(col) - LENGTH(REPLACE(col, ' ', '')) + 1 = 2
          THEN SUBSTRING_INDEX(col, ' ', -1)
        WHEN LENGTH(col) - LENGTH(REPLACE(col, ' ', '')) + 1 > 2
          THEN SUBSTRING_INDEX(col, ' ', -2)
        ELSE NULL
      END AS state
    FROM bad_data) a
  LEFT JOIN (SELECT 'Alabama' AS state 
                  UNION ALL 
                  SELECT 'Arizona' 
                  UNION ALL 
                  SELECT 'Arkansas' 
                  UNION ALL 
                  SELECT 'California' 
                  UNION ALL 
                  SELECT 'Colorado' 
                  UNION ALL 
                  SELECT 'Connecticut' 
                  UNION ALL 
                  SELECT 'Delaware' 
                  UNION ALL 
                  SELECT 'Florida' 
                  UNION ALL 
                  SELECT 'Georgia' 
                  UNION ALL 
                  SELECT 'Guam' 
                  UNION ALL 
                  SELECT 'Hawaii' 
                  UNION ALL 
                  SELECT 'Idaho' 
                  UNION ALL 
                  SELECT 'Illinois' 
                  UNION ALL 
                  SELECT 'Indiana' 
                  UNION ALL 
                  SELECT 'Iowa' 
                  UNION ALL 
                  SELECT 'Kansas' 
                  UNION ALL 
                  SELECT 'Kentucky' 
                  UNION ALL 
                  SELECT 'Louisiana' 
                  UNION ALL 
                  SELECT 'Maine' 
                  UNION ALL 
                  SELECT 'Maryland' 
                  UNION ALL 
                  SELECT 'Massachusetts' 
                  UNION ALL 
                  SELECT 'Michigan' 
                  UNION ALL 
                  SELECT 'Minnesota' 
                  UNION ALL 
                  SELECT 'Mississippi' 
                  UNION ALL 
                  SELECT 'Missouri' 
                  UNION ALL 
                  SELECT 'Montana' 
                  UNION ALL 
                  SELECT 'Nebraska' 
                  UNION ALL 
                  SELECT 'Nevada' 
                  UNION ALL 
                  SELECT 'New Hampshire' 
                  UNION ALL 
                  SELECT 'New Jersey' 
                  UNION ALL 
                  SELECT 'New Mexico' 
                  UNION ALL 
                  SELECT 'New York' 
                  UNION ALL 
                  SELECT 'North Carolina' 
                  UNION ALL 
                  SELECT 'North Dakota' 
                  UNION ALL 
                  SELECT 'Ohio' 
                  UNION ALL 
                  SELECT 'Oklahoma' 
                  UNION ALL 
                  SELECT 'Oregon' 
                  UNION ALL 
                  SELECT 'Pennsylvania' 
                  UNION ALL 
                  SELECT 'Puerto Rico' 
                  UNION ALL 
                  SELECT 'Rhode Island' 
                  UNION ALL 
                  SELECT 'South Carolina' 
                  UNION ALL 
                  SELECT 'South Dakota' 
                  UNION ALL 
                  SELECT 'Tennessee' 
                  UNION ALL 
                  SELECT 'Texas' 
                  UNION ALL 
                  SELECT 'Utah' 
                  UNION ALL 
                  SELECT 'Vermont' 
                  UNION ALL 
                  SELECT 'Virginia' 
                  UNION ALL 
                  SELECT 'Washington' 
                  UNION ALL 
                  SELECT 'West Virginia' 
                  UNION ALL 
                  SELECT 'Wisconsin' 
                  UNION ALL 
                  SELECT 'Wyoming') b ON b.state = a.state) c