CODEWITHSUNDEEP
mysql
Olaseni
Olaseni

Reputation: 7916

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

Upvotes: 2

Views: 2939

Answers (4)

True Soft
True Soft

Reputation: 8786

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

Upvotes: 1

Salil
Salil

Reputation: 47472

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

Upvotes: 0

Volker
Volker

Reputation: 33

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.

Upvotes: 0

Brian Hooper
Brian Hooper

Reputation: 22044

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.

Upvotes: 0

Related Questions