Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Issue on pattern matching in Oracle

 
Ravindranath Chowdary
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi friends,
I have a query to pick the details from test table as follows...

SELECT key FROM test WHERE key like 'ib_b';

on executing this query i am getting the following records

ib_b
ibbb
ibnb
ibhb
ib_b

I need to get only that matches 'ib_b' only. I think here Pattern matching happens.

Can anyone give an idea how to disable the pattern matching in Oracle.
I have so many of the web pages using this query. So, i need a generic solution for this.


Thanks,
Ravindra.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34410
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ravindra,
"like" is the operator for pattern matching. "=" is the operator for string comparison. The following query does what you want.

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34410
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to our JDBC forum since this isn't Oracle specific.
 
Baseet Ahmed
Ranch Hand
Posts: 225
Java Notepad Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ravindra,

You can use escape keyword of Oracle to avoid this kind of problem.
Use following query:
SELECT key FROM test WHERE key like 'ib$_b' escape '$';

Note that you can use any special character(insteadof $) for escaping pattern match.

Hope this helps.


***********************************
Tip: Sincerity is mother of all acheivements.
***********************************
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic