We number our animals with a 1-2 digit string prefix followed by a number. the total string length is 6.

When people enter new IDs, I'd like them to supply a string prefix, and write a SQL string to calculate the next ID in the series. The difficultly is that IDs can either be 1 character + 5 digits or 2 / 4. the patterns are:

rhXXXX
rXXXXX
cyXXXX

In a similar situation elsewhere I did this:

SELECT cast(SUBSTRING(MAX(Id), "+prefix.length+", 6) AS INTEGER) as number FROM study.Demographics d WHERE Id LIKE '" + prefix + "%'"

However, this breaks down b/c if the prefix is 'r', it will return IDs starting with both 'r' or 'rh'. the substring function throws an error. are there other ways to approach this? some questions i had were:

1. without using a regular expression, can I make a smarter pattern matching in the WHERE clause than i'm doing?
2. is there a way to write "d.Id like 'r% AND 'the rest of the ID is numeric'" in labkey SQL?
3. assuming I cannot write a smarter WHERE clause, can I approach the SUBSTRING better?

Note: I'm passing this to JS code, so I could do some processing there. I'd like to offload whatever I can to SQL if possible though. I was considering something like:

SELECT SUBSTRING(MAX(Id), "+prefix.length+", 6) as max, SUBSTRING(min(Id), "+prefix.length+", 6) as min,FROM study.Demographics d WHERE Id LIKE '" + prefix + "%'"

then let the client take these 2 values and figure out which to use. this or something similar might work, but it seems fragile.