0

i have username in my database like abc,abc1,abc2,abc3. i want to retireve name abc3. my query is : select username from users where REGEXP '^abc[0-9]*$' order by userid limit 0,1.

in detail:- i have to fetch user whose name starts with abc and after that there must be only digits. and the one who have largest digit after abc i have to find that. i know largest userid will have largest digit after abc. so i have to find out all users with username strats with abc and after that contains digiits.

i have to apply this logic to make custom user names.for example if user firstname is ab and lastname is cat. then username will be abc. if abc exists in database then username will be abc1. if abc1 exists in database then username will be abc2.so on...

how to i rewrite my query to make it faster.

Monika Yadav
  • 381
  • 2
  • 12
  • 2
    `WHERE username='abc3'` ? (We really need more info to know what you are *actually* trying to do.) – Bart Friederichs Jan 30 '14 at 07:49
  • Looks like the username consists of 2 values, a prefix and a sequence number. Add these in the table as 2 separate columns and query for the max value for a prefix. – Kwebble Feb 03 '14 at 15:48

2 Answers2

0

You can use the LIKE query. LIKE 'abc%'.

Prasanth
  • 5,230
  • 2
  • 29
  • 61
  • no, i have to fetch user whose name starts with abc and after that there must be only digits. and the one who have largest digit after abc i have to find that. i know largest userid will have largest digit after abc. so i have to find out all users with username strats with abc and after that contains digiits. – Monika Yadav Jan 30 '14 at 08:09
  • i have to apply this logic to make custom user names.for example if user firstname is ab and lastname is cat. then username will be abc. if abc exists in database then username will be abc1. if abc1 exists in database then username will be abc2.so on... – Monika Yadav Jan 30 '14 at 08:12
0

If you can be sure the first characters are only letters, and afterwards are only numbers, then you might try:

 select max(cast(substr(username, length('abc')) as int)) as 'number' from user where username like 'abc%'

But this woudn't work if there might be an user like abc1def0, because this would return the number 10 in the query above (when mysql converts strings to integers it drops anything that is not a digit and then converts the result).

You should also create an index for the username column, if you don't have one already.

Update

This wouldn't work if you have a bigger textual part, like abcd11 -- for that you need an extra check, like where username like 'abc%' and length(username)=length( cast(substr(username, length('abc')) as int)).

But that might not be faster than a regex...

Capilé
  • 2,038
  • 17
  • 14