Oracle Query to get values ending in _0

the regexp_like also offers a function based index search if you wrap it in a case

create table tb_documents (doc varchar2(200), docno varchar2(200));

insert into tb_documents(doc, docno) 
select 'A'||level,'doc_'||lpad(trunc(dbms_random.value(1, 2000)), 4, '0') from dual
connect by level <= 10000;

insert into tb_documents(doc, docno) 
select 'A'||level,'doc_'||trunc(dbms_random.value(1, 10)-1) from dual
connect by level <= 100;

commit;

set autot trace
set lines 200 pages 5000
select count(*) n from tb_documents 
where regexp_like(docno, '_0$');  

         N
----------
         9
1 row selected.

Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=9 Card=1 Bytes=102)
   1    0    SORT AGGREGATE (Card=1 Bytes=102)
   2    1      TABLE ACCESS FULL TESTER.TB_DOCUMENTS (Cost=9 Card=9 Bytes=918)

create index ix_docno on tb_documents (case when regexp_like(docno, '_0$') then 1 end);

set autot trace
set lines 200 pages 5000
select count(*) n from tb_documents 
where case when regexp_like(docno, '_0$') then 1 end = 1;  

Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=1 Card=1 Bytes=3)
   1    0    SORT AGGREGATE (Card=1 Bytes=3)
   2    1      INDEX RANGE SCAN TESTER.IX_DOCNO (Cost=1 Card=9 Bytes=27)

Select allOpen in new window

Thanks . How can make REGEXP_LIKE  to see if any document ending with underscore and  any alphanumeric?
 
 For example   anything ending  like   _1     _2    _9   ......     _a  _A ............    _z  _Z

For searching strings ending with an underscore and an alphanumeric, you may use

select  docno 
from    tb_documents 
where   regexp_like(docno, '[_][[:alnum:]]');

Select allOpen in new window

you still need the $ at the end

with x as (
  select 'a_1_' doc from dual union
  select 'b_a' from dual union
  select 'c_9' from dual union 
  select 'aaa' from dual)
select * from x
where regexp_like(doc, '_[[:alnum:]]$'); 

Select allOpen in new window

@Geert G - Indeed. Good spot.

>>> the regexp_like also offers a function based index search if you wrap it in a case

Yes, if you want to make a function-based index on case, then you could do so with LIKE as well.

Just as a point of clarification.  The reason that you get more results than you expected is because the underscore is a single character wild card to LIKE.

If someone else pointed that out, sorry, I missed it.  I looked but I didn't see it.

The documentation (here), does explain it.  That is the 12.2 documentation, but it has been like that as long as I have been using Oracle.

select * from TB_Documents
where DocNo like '%\__' escape '\';

Nore - that's two underscores, the first one is escaped meaning look for an underscore and the second one means any single character following it.