SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

using regexp like or regexp instr

Gor_MahiaApr 20 2023 — edited Apr 20 2023

Hi,
iam using REGEXP_LIKE to filter a set of data but its not working as expected. When i search for "104." it return "104", "w1045" etc. . It doesn't interpret the extended characters like "."(dot). see sample below:

using Oracle 12 g Enterprise

sample records:

-----------------
w1045T
K1046
w21047
M21048
p21049
104.1
104.11
104.12
104.21
104.4
104.6
104.7

w21044

my query is:

select * from myRatings p
where case when INSTR(p.rating_number,:P_SEARCH) >= 1 then 1 else 0 end =1

i tried also:

select * from myRatings p where
case when REGEXP_LIKE(p.rating_number,:P_SEARCH,'i') then 1 else 0 end =1

Any help on this is appreciated.

This post has been answered by Solomon Yakobson on Apr 20 2023

Jump to Answer

Added on Apr 20 2023

#pl-sql

6 comments

251 views

1 mention