using regex in mysql based on middle of string value -

i have system uses personalized urls (i.e. johnsmith.mywebsite.com). in database, these values stored in "purl" column.

if 6 months now, john smith need put system, add 1 name purl becomes johnsmith1.mywebsite.com.

my database has grown large checking manually real time consumer. so, i'd make quick app can enter in names, check against database return number should add onto end.

how can use mysql search if johnsmith[any number].mywebsite.com exists while not getting positive hit on purl johnsmithson1234.mywebsite.com?

so basically, need exact match on name, , domain, need latest number used can add 1 it.

mysql doesn't regex searches you're asking. however, can in application logic. this:

select * table purl 'johnsmith%';

then loop on results in app , see if have numbers the purl column.

also, served downcase in purl column since dns case insensitive , mysql not. may have times johnsmith being searched johnsmith in db , have no results.

edit:

apparently mysql allow regex searches. 1 highest number add "order purl desc limit 1"