Question: The following command has been executed:

```
CREATE TABLE film (
  film_id NUMBER(5) NOT NULL,
  title varchar2(255),
  description varchar2(255),
  release_year NUMBER(4) DEFAULT NULL,
  language_id NUMBER(3) NOT NULL,
  original_language_id NUMBER(3) DEFAULT NULL,
  rental_duration NUMBER(3) DEFAULT 3 NOT NULL,
  rental_rate NUMBER(4,2) DEFAULT '4.99',
  length NUMBER(5) DEFAULT NULL,
  replacement_cost NUMBER(5,2) DEFAULT '19.99' NOT NULL,
  rating varchar2(8) DEFAULT 'G',
  special_features varchar2(255) DEFAULT NULL
);

```

Assume an abundance of data has been populated.

I want to construct a query to select *the ​first 200 film titles*​ (*in ascending alphabetical order*) where the film takes place in a `Boat`. Assume that the film location is contained within the film table and attribute, description. Additionally, it is always after the first occurrence of the word `in` and finishes at the end of the sentence. For example in the following film description: 

```
​A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher 
in The Canadian Rockies

```

The location is `The Canadian Rockies`.

As long as the location contains the word `Boat` it's fine. `Boat` can be any boat like *U-Boat*, *JET Boat* etc

Answer: 
You can use instr function for the words `boat` and `in` with such a combination :

```
select film_description
  from
(
with t(film_description) as
(
 select '... a Teacher in The Canadian Rockies' from dual union all
 select 'Boat on the river' from dual union all
 select 'on the river in a Boat' from dual union all
 select 'on the river in a U-Boat' from dual union all
 select 'on the river in a JET Boat' from dual    
)
select t.film_description, dense_rank() over (order by t.film_description) as dr
  from t
 where instr(lower(t.film_description),'boat') > instr(t.film_description,' in ')
   and instr(t.film_description,' in ') > 0
)   
where dr <= 200;

```

*or you may use the following provided that your **Oracle DB** version is **12c*** :

```
with t(film_description) as
(
 ................
 ................ 
 select '... a Teacher in The Canadian Rockies' from dual union all
 select 'Boat on the river' from dual union all
 select 'on the river in a Boat' from dual union all
 select 'on the river in a U-Boat' from dual union all
 select 'on the river in a JET Boat' from dual    
)
select t.film_description
  from t
 where instr(lower(t.film_description),'boat') > instr(t.film_description,' in ')
   and instr(t.film_description,' in ') > 0
fetch first 200 rows only;

```

[***Rextester Demo***](https://rextester.com/USF7486)
Maybe the query can look like this:

```
SELECT * 
FROM "FILM" 
WHERE UPPER("DESCRIPTION") LIKE '%IS%BOAT%'
AND ROWNUM<200;

```