Q:

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, 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:

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

