Search code examples
sqlms-accessmaxmemo

Picking the maximum value from an alpha-numeric column in MS Access

I've a table that has a column with data type 'Memo'.
This field contains values like "X003", "X0022", "X002", "X0030", "X0031", "X004". In short the first "X00" part is common, and the rest part is a integer.
I want to select that row which contains the largest integer in the second part via a sql query. How should i write the sql?
If it was only a integer, I would use "MAX()" in sql to get the maximum value. But it's different from that. What should I do? Please help me.

Solution 
  • SELECT max(val(mid([col],3))) FROM thetable;

    I.e. Select the maximum of the value after the 3rd character, cast to a number (or 0 if not convertible).