-2

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_%'
peter
  • 8,158
  • 21
  • 66
  • 119
  • 5
    Possible duplicate of [Underscore is not working in oracle like clause](https://stackoverflow.com/questions/21380261/underscore-is-not-working-in-oracle-like-clause) – Ben Mar 18 '19 at 18:08

2 Answers2

1

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 '_'
haldo
  • 14,512
  • 5
  • 46
  • 52
-2

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

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