Check if number in varchar2 is greater than n

Question

I've got a Varchar2 field which usually holds two alphabetic characters (such as ZH, SZ, AI,...). Let's call it FOO.

Certain datasets save A or A1 - A9 into the same field. I need to select all rows except exactly those. I used the function substr to separate the number from the A. So far so good, < or > don't seem to work correctly with the "number-string".

How can I achieve this without converting it to a number? Is there an easier solution?

I haven't found anything on the internet and I reached my limit trying it myself.

This is my WHERE clause so far:

WHERE (substr(FOO, 0, 1) != 'A'
 or (substr(FOO, 0, 1) = 'A' AND substr(FOO, 1, 1) > '9'));

It returns all the rows without restrictions.

The only solution I found:

WHERE (FOO NOT IN ('A', 'A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9'));

But this is not optimal if, somewhere in the future, there will be A1 - A50. I would have to add 51 strings to my WHERE clause. And, since the query is in source code, also the code readability would get worse.

The solution should work on ORACLE and SQL Server. Thanks in advance

Answer 1

(substr(FOO, 0, 1) = (substr(FOO, 1, 1) - Oracle starts with 1 (not 0).

So you should use substr(FOO, 2, 1) to get the second symbol.

However, it won't work in SQL Server which has SUBSTRING (not SUBSTR).

if you're ready to use different approaches in the different DBs you can also try regular expressions:

Oracle

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

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

Examples of FOOs which 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 is 'A', the others are 3 digits)
A123b -- doesn't match (the last symbol should be a digit)
A1234 -- doesn't match (there should be 1,2 or 3 digits an the end)
A12 -- match
A1 -- match

SQL Server
REGEXP_LIKE conversion in SQL Server T-SQL

Answer 2

If your requirement is to include all alphabetic values except 'A' alone, consider using a LIKE expression so that it will work with any ANSI-compliant DBMS:

WHERE FOO <> 'A' AND FOO NOT LIKE '%[^A-Z]%'