0

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

Alex
  • 781
  • 10
  • 23
  • 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). – Multisync Nov 27 '14 at 10:24
  • Thanks, I didn't know that. It works! Now I just need to make an extra clause for SQL Server - but still better than a `NOT IN()`. @Multisync Would you please post your solution as an answer, so I can mark it as answered. Thanks for the fast help though! – Alex Nov 27 '14 at 10:29

2 Answers2

2

(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

Community
  • 1
  • 1
Multisync
  • 8,657
  • 1
  • 16
  • 20
0

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]%'
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Unfortunately it isn't. I need to get all alphabetic values except 'A' and 'A[1-9]'. If you got a better solution than substr() or regex, tell me! – Alex Nov 27 '14 at 17:05
  • To state it another way, the code I posted will exclude all non-alphabetic values and the value 'A'. Can you provide an example of a value not addressed by this expression? – Dan Guzman Nov 27 '14 at 17:38
  • I tested it (on Oracle), and it does exclude the value `A` but not the value `A5`, for example. – Alex Nov 28 '14 at 07:22
  • 1
    The LIKE pattern I posted works as expected with SQL Server but not Oracle. I don't think there is a single solution that will work in both products. 'REGEXP_LIKE' does not exist in SQL Server out-of-the-box. ANSI SQL includes a `SIMILAR` predicate which allows a regular expression pattern but that isn't implemented in either product. – Dan Guzman Nov 28 '14 at 20:37