mySQL regex selecting string and incremental number

Question

Hi so I have email addresses saved for example in below format:

[email protected]
[email protected]
[email protected]

As can be seen everything is same in these email except for the number before @ sign. I want to select the last biggest email address with that number in this case [email protected] since 3 is biggest number in these emails.

I am not much aware about regex but I tried this:

SELECT id, email FROM tableName WHERE email regexp 'NEWUSER(\d+)@domain.com'
ORDER BY email DESC LIMIT 1

But it didn't work obviously regex isn't correct :( Can anyone help on how to select row with biggest number email out of these please ?

@zerkms: Yeah regex to parse emails in format NEWUSER(\d+)@domain.com where \d is a number you know but it doesn't return any rows obviously regex isn't correct — Dev01
– Dev01, Commented Sep 13, 2013 at 11:35
You want the last line ? i mean largest number will always at the last line ? — Jageen
– Jageen, Commented Sep 13, 2013 at 11:38
@Jageen: not necessaly it will always be last line, it could be anywhere but i want to get row with largest number in email. I have updated the question — Dev01
– Dev01, Commented Sep 13, 2013 at 11:39

Vaishu · Accepted Answer · 2013-09-13 12:02:34Z

1

 SELECT * FROM tableName where email like 'NEWUSER%' ORDER BY CAST(SUBSTR(email FROM 8) AS UNSIGNED) desc limit 1

edited Sep 13, 2013 at 12:02

answered Sep 13, 2013 at 11:45

Vaishu

2,3733 gold badges23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dev01 Over a year ago

well it works with warnings but still not ideal solution since it doesn't consider my explicit string NEWUSER and therefore may also return incorrect rows such as [email protected]

Wietze314 Over a year ago

This only works when the email address pattern is always the same. It breaks when there are addresses like [email protected]

Vaishu Over a year ago

You want sort numerals only? [email protected]

Dev01 Over a year ago

@jey: I only want to get rows with NEWUSER term in email field

Vaishu Over a year ago

@Dev01:SELECT * FROM tables where website like 'NEWUSER%' ORDER BY CAST(SUBSTR(website FROM 8) AS UNSIGNED) desc

Collectives™ on Stack Overflow

mySQL regex selecting string and incremental number

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related