I am developing a web service where a term of search is given, then I return all the matches from employees table. In MySQL I used to do the following:
SELECT * FROM employees WHERE MATCH(name, lastname, nickname) AGAINST('billy jops');
The query finds records that match "billy jops", "billy" or "jops" in name, lastname or description columns of employees tables.
Now I need to emulate this searching pattern in Oracle or at least a little bit closer.
MATCHES SQL Query
I have read about Querying with Oracle Text, and looks like MATCHES SQL Query is what I need. However, looks like the search index is applied just to an unique column:
create table queries (
query_id number,
query_string varchar2(80)
);
insert into queries values (1, 'oracle');
insert into queries values (2, 'larry or ellison');
insert into queries values (3, 'oracle and text');
insert into queries values (4, 'market share');
create index queryx on queries(query_string)
indextype is ctxsys.ctxrule;
select query_id from queries
where matches(query_string,
'Oracle announced that its market share in databases
increased over the last year.')>0
This query will return queries 1 (the word oracle appears in the document) and 4 (the phrase market share appears in the document), but not 2 (neither the word larry nor the word ellison appears, and not 3 (there is no text in the document, so it does not match the query).
Could you please let me know how to adjust this query to be compatible with more than one column?