MySQL选择一列直到数字 [英] MySQL Selecting a column until number

问题描述

基本上我想从一列中进行选择，直到字段中有一个数字.例如，我有看起来像

Basically I want to select from a column until there is a number in the field. For example I have postcode data which looks like

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

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

I want to be able to just get the first letters e.g. RG, SW, M. So select the data until it reaches a non-alpha character. I want to do this so I can group it by the first alpha characters

目前我的查询是这样的:

Currently my query is like this:

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

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

It groups based on the column upto the first space

谢谢！

推荐答案

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

Assume you have up to 3 leading letters in your postal codes, you can try this:

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

这篇关于MySQL选择一列直到数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！