php - MySQL选择列直到数字

Question

基本上我想从列中选择，直到字段中有数字为止。例如我有邮政编码数据，看起来像

+----------+
| postcode |
+----------+
| RG41 5LY |
| RG7 6LO  |
| SW 1AA   |
| M 3BV    |
+----------+

我希望能够只获取第一个字母，例如RG, SW, M. 所以选择数据直到它到达一个非字母字符。我想这样做，以便我可以按第一个字母字符对其进行分组

目前我的查询是这样的:

    SELECT COUNT(*) as count, postcodeArea FROM Client c
    (SELECT UPPER((SUBSTRING_INDEX(postcode, ' ', 1))) AS postcodeArea

它根据列到第一个空格进行分组

谢谢!

Answer 1

假设您的邮政编码中最多有 3 个前导字母，您可以试试这个:

SELECT COUNT(*) tot, pcode FROM (
  SELECT CASE 
     WHEN postcodearea REGEXP '^[A-Z][A-Z][A-Z].*' THEN SUBSTRING(postcodearea,1,3) 
     WHEN postcodearea REGEXP '^[A-Z][A-Z].*' THEN SUBSTRING(postcodearea,1,2)
     WHEN postcodearea REGEXP '^[A-Z].*' THEN SUBSTRING(postcodearea,1,1)
     ELSE '' END AS pcode
   FROM client
  ) e
  GROUP BY pcode