Unanswered: Using wildcards to specify numeric values in SQL
I am curious if there is a way to specify that the last three digits of a value you wish to retrieve are numeric.
Here is my SQL -
SELECT max(id) FROM mytable WHERE id like '123456_%';
The problem is that I need to make sure that the values that are retrieved after the underscore are numeric. For example, if the values 123456_001, 123456_002, 123456_003 and 123456_00A are present, then the value I need back is 123456_003. However, the value that is returned back is 123456_00A because of max function treats the A as "greater" than the 3.
Is there a way to ensure that I only get back the values where the last 3 digits (or even better, the three digits immediately following the underscore) are numeric.
I have tried using the following, but it still doesn't work:
SELECT max(id) FROM mytable WHERE id < '123456_999';
One more question. Right now this just gets me back the max for all of the id column. But, I can't specify the like to match it to the id value. For example, I still need to make sure that the max value is only for the string that I specify. In other words, I still need to make sure that it is like the value I compare it to.