Strip out digits/numeric chars from a mysql string column

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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