从mysql字符串列中去除数字/数字字符 [英] Strip out digits/numeric chars from a mysql string column

问题描述

我在mysql表中有一些列，这些人的名字存储为字符串和递增数字的组合，以确保唯一性，所以我的名字也这样存储:

I have columns in a mysql table that stores names of people as combinations of strings and incremented digits for uniqueness, so I have names stored as so :

Patrick, Patrick1, Patrick2, ..... Patrick10, David, David2, .... David5

我如何只检索字母名称本身而没有数字?假设我想按不同的名称分组，并按组进行计数，那么我得到的结果类似于以下内容.

How do I retrieve just the alpha name itself, without the digits? Say I want to group by the distinct names, and count per group, so I get a result resembling the following.

name    | frequency
-----------------
Patrick | 10
David   | 5

推荐答案

解决方案是这样的:(它看起来不太好，但是有效)

A solution would be this:(it doesn't look to good, but it works)

SELECT 
  TRIM(TRAILING '0' FROM 
    TRIM(TRAILING '1' FROM 
      TRIM(TRAILING '2' FROM 
        TRIM(TRAILING '3' FROM 
          -- ... 
            TRIM(TRAILING '8' FROM 
              TRIM(TRAILING '9' FROM name)))))) AS name
FROM your_table

然后您可以从结果中选择GROUP BY:

Then you can select with GROUP BY from the result:

SELECT name, count(*) AS frequency FROM (
-- previous select
) AS t
GROUP BY name

这篇关于从mysql字符串列中去除数字/数字字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！