Extract numbers / numeric characters from mysql row column

Question

I have columns in a mysql table that stores the names of people as combinations of strings and extra digits for uniqueness, so I have names that are stored like this:

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

How to get only alpha name without numbers? Let's say I want to group different names and count each group, so I get a result similar to the following.

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

Answer 1

The solution would be this: (it doesn't look 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 GROUP BY from the result:

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

Answer 2

I will think about this a bit, but I would recommend that if you need a distinguishing number, you keep it in a different column. Thus, you will not have such difficulties.

Answer 3

you can use udf .

and then try something like follwing

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

Answer 4

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

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

I would also think that it is better to do what Brian suggested.