'How to use special character '_' in where clause of oracle

I want to get the columns named 'pl_name' contains _5M_. So i did following query ,but it is giving all the values but i want pl_name like AR_5M_testclient_986

Query used is as below

select *from mn_table where status='Y' and upper(pl_name) like'%_5M_%'
c++sharp.netoracle


Solution 1:[1]

Treat underscore as a wildcard entry. Use [_].

select * from mn_table where status='Y' and upper(pl_name) like '%[_]5M[_]%';

Solution 2:[2]

The underscore _ is treated as a wild card in SQL, so it's actually trying to match any character.

In Oracle you can use an escape character to escape special characters:

select * from mn_table where status='Y' and upper(pl_name) like'%\_5M\_%' ESCAPE '\'

Alternatively, from Oracle LIKE docs:

You can also search for the escape character itself by repeating it. For example, if @ is the escape character, then you can use @@ to search for @.

select * from mn_table where status='Y' and upper(pl_name) like'%__5M__%' ESCAPE '_'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Varun Srinivasan
Solution 2

Related Questions