I've a column in a table which has values such as:

'EV000450'
'EV000451'
'EV000452'

etc.

This is fine: the expression is always of the format '^EV[0-9]{6}$', with the numeric portion auto-incrementing for each new record. I'm assured that this will always be the case (I have no faith in this assertion, but let's assume the client is telling the truth, for the moment, shall we...).

I take it there will be no problem my determining what the new value for this field will be when INSERTing a new record, by simply asking what the MAX() value for the field is, and adding one to the numeric portion of the value that's returned? What I mean is, is that there's no aspects about how MySQL regards the maximum value of mixed alpha-numeric field (that matches the RegExp I've given above), that is likely to come around the corner and bite me later on. Assuming that:
A) as I say, the fields continue to be in this format,
and
B) we have a safe margin of time to go between now and the point when the value EV999999 is taken?

If anyone has come across this kind of thing before, it'd be much apreciated.

Dan

As long as the integer portion of your string is always left-padded with zeros, you should not have a problem.

MySQL will assume MAX as the last item of an alphabetical sort. You can also think of this as the first item in the result set that's sorted by this field in DESC order.

An alpha numeric sort works using ASCII character values, not interpreted numeric values. Therefore, you must be cautious because this means that 'a' is GREATER THAN 'Z'.

The "EV" portion of the string will always, obviously, be of equal value when sorting. This means that the sort is completely dependent upon the values of the sequential digit characters following "EV". The ASCII values for these characters are sequential and increasing, just like the values for the digits these characters represent.


Long story short, no, you shouldn't have a problem -- as long as the format of the field does NOT change.
If you get to "EV999999" you'll have to deal with the wrapping case. ASCII places digit characters before letter characters, so you might be able to fudge your results by using letters for the higher numbers:

EV999999 + 1 = EV99999A

etc.


Take care,

Nik
http://www.bigaction.org/

Cool, cheers, Nik.

I'm using:

    //To generate our value: find out the biggest current value...
    $sql = "SELECT MAX(value) FROM table";
    $result = $db->getOne($sql);

    //Determine the numeric portion of that result...
    $numeric_portion = substr($result, 2);

    <new value> = "EV" . sprintf('%06u', ++$numeric_portion);

To increment the value.

(...and Tonight I'm to party like it's EV999999!)

That looks fine to me. It's not necessary, but you might want to explicitly cast the "numeric portion" of the string as an integer.

$numeric_portion = (int)substr($result, 2);


Take care,

Nik
http://www.bigaction.org/

Very true. Unfortunately, I've inherited a rather large and untidy pile of code, and so my sense for the aesthetics of code is perhaps wearing a little thin ;).