Strip out digits/numeric chars from a mysql string column

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, 开发者_JS百科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

Then you can select with GROUP BY from the result:

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

I'll have a little think about that, but I would recommend that if you need a distinguishing number, you keep it in a different column. That way, you won't have difficulties of this sort.

You can "chain" the replace command like this (this will remove the digits 0,1,2 in the query). You can expand this for the other digits, but I don't know if this will perform very well on large datasets:

select replace(replace(replace(Name,"0",""),"1",""),"2","") from users;

I would think also, it will be better to do what Brian suggested.

you could use a udf.

and then try Something like follwing

select REGEX_REPLACE(name, [0-9], '') as Name, Count(Name) 
      from tableName  
      Group by Name