Mathis Mathis - 9 days ago 5
SQL Question

How to compare with records in a table using like operator with square brackets in oracle 10g?

select * from emp where ename like '[bsp]%';


It will display the output by comparing the first letter of each records in ename column with with b,s and p in MS SQL Server.

How to achieve the same output using oracle 10g with single like operator?

Answer

The LIKE operator in SQL only supports two wildcards: % for any number of characters and _ for exactly one character. It does not support regular expressions. The LIKE operator in Microsoft's T-SQL extends the SQL standard and offers a poor man's regular expression support.

In Oracle you need to use a proper regex:

select *
from emp
where regexp_like(ename, '[bsp]$', 'i');

For more details see the manual: http://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions007.htm#i1048942

Note that the regular expressions used by regexp_like() are very different (and more complicated) to the simple "range" expression that T-SQL offers.

Another way of expressing this would be:

select *
from emp
where lower(substring(ename, -1)) in ('b', 's', 'p');
Comments