Find matching strings in table column Oracle 10g

Question

I am trying to search a varchar2 column in a table for matching strings using the value in another column. The column being searched allows free form text and allows words and numbers of different lengths. I want to find a string that is not part of a larger string of text and numbers.

Example: 1234a should match "Invoice #1234a" but not "Invoice #1234a567"

Steps Taken: I have tried Regexp_Like(table2.Searched_Field,table1.Invoice) but get many false hits when the invoice number has a number sequence that can be found in other invoice numbers.

Answer 1

Suggestions:

1. Match only at end:

   REGEXP_LIKE(table2.Searched_Field, table1.Invoice || '$')
   

2. Match exactly:

   table2.Searched_Field = 'Invoice #' || table1.Invoice
   

3. Match only at end with LIKE:

   table2.Searched_Field LIKE '%' || table1.Invoice