sql - Check if number in varchar2 is greater than n -

i've got varchar2 field holds 2 alphabetic characters (such zh, sz, ai,...). let's call foo.

certain datasets save a or a1 - a9 same field. need select rows except those. used function substr separate number a. far good, < or > don't seem work correctly "number-string".

how can achieve without converting number? there easier solution?

i haven't found on internet , reached limit trying myself.

this where clause far:

where (substr(foo, 0, 1) != 'a'  or (substr(foo, 0, 1) = 'a' , substr(foo, 1, 1) > '9')); 

it returns rows without restrictions.

the solution found:

where (foo not in ('a', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9')); 

but not optimal if, somewhere in future, there a1 - a50. have add 51 strings where clause. and, since query in source code, code readability worse.

the solution should work on oracle and sql server. in advance

(substr(foo, 0, 1) = (substr(foo, 1, 1) - oracle starts 1 (not 0).

so should use substr(foo, 2, 1) second symbol.

however, won't work in sql server has substring (not substr).

if you're ready use different approaches in different dbs can try regular expressions:

oracle

where not regexp_like(foo, '^a[1-9]{1,3}$') 

^ begining of string
$ end of string
[1-9] digit 1 9
{1,3} repeat previous expression 1,2 or 3 times

examples of foos match / not match '^a[1-9]{1,3}$' a123 -- may match / may not (depending on nls settings regarding case sensitivity)
a123 -- match (the first symbol 'a', others 3 digits)
a123b -- doesn't match (the last symbol should digit)
a1234 -- doesn't match (there should 1,2 or 3 digits end)
a12 -- match
a1 -- match

sql server
regexp_like conversion in sql server t-sql