Choose the biggest number in string in column

Question

I have some complicated task. I have some data in email column:

borispil-admin@fff.com

borispil2-admin@fff.com

borispil5-admin@fff.com

I need to get biggest number. In example its 5.

P.S. some time names can ends with number:

kuhari24-admin@fff.com

kuhari242-admin@fff.com

kuhari243-admin@fff.com

Here the real name is kuhari24. And biggest number is 3. Here is my answer, but it very ugly. Have some one better idea?

SELECT REPLACE(
    (
        SELECT REPLACE(
            (
                SELECT MAX(email) 
                FROM account 
                WHERE email LIKE 'borispil%'
            )
        , '--admin@fff.com', '')
     )
, 'borispil', '') AS maxNumber

*"I'm working on MySQL"* Then please don't tag other RDBMS. I have removed [tag:sql-server]. — Larnu, Sep 17 '20 at 09:59
How do you know if `abc24-admin` is a user or another `abc2` alternative? — jarlh, Sep 17 '20 at 10:43

Bernd Buffen · Answer 1 · 2020-09-17T10:28:36.857

-1

You can easy use REPLACE and +0 to find the Integer after a Name

SELECT field-realname, MAX(REPLACE(fiel-with-email,field-realname,'') + 0) as maximum_val FROM your_table group by fiels-realname;

Sample

MariaDB [(none)]> select REPLACE('kuhari243-admin@fff.com','kuhari24','') + 0;
+------------------------------------------------------+
| REPLACE('kuhari243-admin@fff.com','kuhari24','') + 0 |
+------------------------------------------------------+
|                                                    3 |
+------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [(none)]>

edited Sep 17 '20 at 10:28

answered Sep 17 '20 at 10:13

Bernd Buffen

12,768
2
20
31

Why o you ownvote this answer ?? – Bernd Buffen Sep 17 '20 at 10:18
It does not do what I need. What if I have borispil-admin@fff.com borispil2-admin@fff.com borispil5-admin@fff.com such rows – Anton Kolosok Sep 17 '20 at 10:22
and you also must have the real name like kuhari24.. So you relace the realname ang group by it and you can give the max. i will add it in my answer . – Bernd Buffen Sep 17 '20 at 10:25

Choose the biggest number in string in column

1 Answers1