mySQL sorting issue with alphanumeric string

Question

I have a purchased database with non-zero-padded alphanumeric golf course "numbers" like ZZ-1, ZZ-2, ZZ-9, ZZ-10...

I need to write a query which will pull the MAX numeric value and MAX when used on a string does not DWIM and sorts ZZ-9 as MAX over ZZ-10.

SELECT MAX( CourseNumber ) AS x
FROM courses
WHERE CourseNumber
RLIKE 'ZZ'

Same problem when only selecting the digits:

SELECT MAX(SUBSTR(CourseNumber, 4)) AS x 
FROM courses WHERE CourseNumber RLIKE 'ZZ'

Anyone have a clever way to do this? I was think it must involve SUBSTR but I couldn't think how to make it work.

Are the alpha prefixes fixed-length, or are there 'PDQ' and 'ABCDFEGFGHIJLFERWEFDSFDS' prefixes as well? Either way, if you're doing a lot of searching/matching on those sub-fields, you may want to invest the time to pre-split those bits out into separate fields so you can do direct matching on those derived fields, otherwise your where clauses are going to turn into a hideous mess of substring/cast clauses. — Marc B
– Marc B, Commented Mar 10, 2011 at 20:07
At present they are all 2 letters, hyphen, numeral. The people my boss bought this data from are idiots though so it may not stay that way forever but a solution assuming that pattern works for me! — jerrygarciuh
– jerrygarciuh, Commented Mar 10, 2011 at 20:12

jerrygarciuh · Accepted Answer · 2011-03-13 14:22:24Z

4

SELECT MAX(
  CAST(
    SUBSTRING(CourseNumber, 4) AS UNSIGNED)
  ) 
FROM courses WHERE CourseNumber RLIKE 'ZZ'

answered Mar 10, 2011 at 20:23

jerrygarciuh

22.3k26 gold badges86 silver badges145 bronze badges

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

Comments

Randy · Accepted Answer · 2011-03-10 20:07:35Z

1

maybe to_number(SUBSTR(coursenumber,3))

answered Mar 10, 2011 at 20:07

Randy

16.7k2 gold badges40 silver badges56 bronze badges

mySQL doesn't have to_number; I believe that's an Oracle function.

@jerry - yep im an Oracle guy at heart :)

Maybe something like SELECT CAST(SUBSTR(CourseNumber), 3) AS UNSIGNED) FROM courses WHERE CourseNumber RLIKE 'ZZ'

Randy - Googling to_number() set me on the right path! Thanks! forums.mysql.com/read.php?10,152446,233605#msg-233605