CODEWITHSUNDEEP
sqlsql-serverregexstringoracle-database
samuraivader
samuraivader

Reputation: 43

Using NOT LIKE in Oracle

This might be a silly question -I hope it isn't- but I still don't get why this code doesn't run in Oracle but runs in MS SQL Server.

The code is: 

SELECT DISTINCT CITY FROM STATION
WHERE CITY NOT LIKE '[AEIOUaeiou]%'
ORDER BY CITY ASC;

The code gives me all the cities that don't start with vowels in SQL Server, but it gives me all the cities when I run it in Oracle.

Why is that?

Upvotes: 3

Views: 5897

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

WHERE CITY NOT LIKE '[AEIOUaeiou]%'

The code gives me all the cities that don't start with vowels in SQL Server, but it gives me all the cities when I run it in Oracle.

Why is that?

The answer to your question is that SQL Server has extended the functionality of LIKE to include character ranges -- sadly leaving out much of what else is useful about regular expressions. Well, actually, SQL Server did not do this. Sybase did, and Microsoft purchased the code base from Sybase. So those two databases support this extended LIKE functionality. (I could add that MS Access also has something similar.)

Most other databases have bona fide regular expression support, supported through functions (such as regexp_like() in Oracle or operators such a '~' in Postgres and regexp in MySQL). They treat a LIKE pattern such as '[AEIOUaeiou]%' exactly like what it says: they would match a string that starts with 12 specific characters -- '[', 'A', and so on. Because no city name starts with [, much less that followed by ten vowels, no city matches the pattern -- and al pass the NOT LIKE logic.

Also, in SQL Server, the comparisons are usually case-insensitive, so '[A]' would match both 'A' and 'a'. You could simplify the expression in that database. On the other hand, comparisons are usually case sensitive in Oracle, so you need to be careful.

I think GMB has explained how to use regular expressions to do what you want.

Upvotes: 2

user5683823
user5683823

Reputation:

Your attempt doesn't work, because in Oracle, the conditions LIKE / NOT LIKE do not work with regular expressions. Instead, there are only two (or three) meta-characters: the % (which you were trying to use, correctly), the underscore which stands in for exactly one character, and possibly an escape character (if you need to escape % or _), which must be declared in the LIKE / NOT LIKE condition. Everything else is literal characters. No city name begins with the substring [AEIOUaeiou] (12 characters). That is what your condition was preventing; so, indeed, it wasn't preventing any names from being included in the output.

GMB gave you a 100% correct solution, in the spirit of your question - using regular expressions.

I only know Oracle, no other db product; but at least in Oracle, standard string functions and conditions are much faster than their regular expression counterparts, and should be used whenever possible. At least when performance is important; I believe it is a good practice to use them whenever possible even when performance is not important, just to form good habits.

Here, a simple and efficient solution using only standard string functions is absolutely possible, and relatively simple.

select city
from   station
where  substr(city, 1, 1) not in ('A', 'E', 'I', 'O', 'U', 'a', 'e', 'i', 'o', 'u')
;

Of course, this can then be modified in various ways; for example, we could use lower() to avoid repeating upper and lower case vowels. Or, even:

select city
from   station
where  'aeiou' not like '%' || lower(substr(city, 1, ,1)) || '%'
;

but I view such code (even if sometimes it may be slightly faster) as gimmicky; best let the code say exactly what you mean, as in the first version. I am not sure there is a difference in performance anyway. (But both versions should be much faster than anything using regular expressions, because regexp uses a significant machinery, no matter how simple the problem is.)

Note that I left out the "distinct" in the select clause since it is totally unrelated to the question. Always try to make questions as "small" as possible, while still fully reflecting the question actually being asked.

Upvotes: 4

GMB
GMB

Reputation: 222442

SQL Server supports a small subset of regular expression in LIKE pattern. Oracle does not, but it has comprehensive regex support with the regexp_* functions.

Here, you can use regexp_like():

SELECT DISTINCT CITY FROM STATION
WHERE NOT REGEXP_LIKE(CITY, '^[aeiou]', 'i')
ORDER BY CITY ASC;

Regex ^[aeiou] means: one of the listed characters at the beginning of the string (which '^' stands for). The third argiment is called the match parameter: 'i' makes the search case insensitive.

Upvotes: 6

Related Questions