CONTAINS in Oracle 11.2

Question

Is it possible to use something working like CONTAINS function from new Oracle? I have 11.2 and would like to do sth like this:

select * from cars
inner join customers on customers.as_id = cars.as_id
where cars.type like 'AUDI' and contains(request, customers.name, 1) > 0;

so as I know I can't use LIKE here because customers.name is not a fixed value. Is there a way to find some workaround for old Oracle?

Answer 1

You can use LIKE as follows:

select * 
  from cars
 inner join customers on customers.as_id = cars.as_id
 where cars.type = 'AUDI' -- You can use = here
   and request like '%' || customers.name || '%';

Note: contains clause is used for finding specific strings within an Oracle Text index. it can not be applied on normal columns.

Answer 2

CONTAINS is an Oracle text function; Oracle Text is supported on Oracle 11g.

Just create an Oracle text context index on the column:

CREATE TABLE cars ( as_id, type, request ) AS
SELECT 1, 'AUDI', 'Requested by alice abbots on 2020-01-07' FROM DUAL;

CREATE INDEX cars__request__textidx ON cars(request) INDEXTYPE IS CTXSYS.CONTEXT;

CREATE TABLE customers ( as_id, name ) AS
SELECT 1, 'Alice Abbots' FROM DUAL;

Then your query:

select * from cars
inner join customers on customers.as_id = cars.as_id
where cars.type like 'AUDI' and contains(request, customers.name, 1) > 0;

Outputs:

AS_ID | TYPE | REQUEST                                 | AS_ID | NAME        
----: | :--- | :-------------------------------------- | ----: | :-----------
    1 | AUDI | Requested by alice abbots on 2020-01-07 |     1 | Alice Abbots

db<>fiddle here