CODEWITHSUNDEEP
sqloracle-database
BK-GG
BK-GG

Reputation: 23

Regex not working in LIKE condition

I'm currently using Oracle SQL developer and am trying to write a query that will allow me to search for all fields that resemble a certain value but always differ from it.

SELECT last_name FROM employees WHERE last_name LIKE 'Do[^e]%';

So the result that I'm after would be: Give me all last names that start with 'Do' but are not 'Doe'.

I got the square brackets method from a general SQL basics book so I assume any SQL database should be able to run it.

This is my first post and I'd be happy to clarify if my question wasn't clear enough.

Upvotes: 1

Views: 1411

Answers (1)

sticky bit
sticky bit

Reputation: 37472

In Oracle's LIKE no regular expressions can be used. But you can use REGEXP_LIKE.

SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (Name, '^Do[^e]');

The ^ at the beginning of the pattern anchors it to the beginning of the compared string. In other words the string must start with the pattern to match. And there is no wildcard needed at the end, as there is no anchor for the end of the string (which would be $). And you seem to already know the meaning of [^e].

Upvotes: 5

Related Questions