Oracle: "grep" across multiple columns?

Question

I would like to perform a like or regexp across several columns. These columns contain tags, keywords, etc. Something that's the equivalent of:

sqlplus scott/tiger @myquery.sql | grep somestring

Right now I have something like

select * from foo where c1 || c2 || c3  like '%somestring%'

but I'm hoping I can get something a bit more general purpose and/or optimized. Any clues appreciated!

Answer 1

Does regexp_like help.

http://www.psoug.org/reference/regexp.html

Answer 2

On 11G you could create a virtual column:

alter table foo add all_text varchar2(4000) generated always as (c1 || c2 || c3);

(See Oracle 11G new features).

Then query:

select * from foo where all_text like '%somestring%'

You could add an index on all_text if it helps performance too (see this answer for when it might help and when not).

Prior to 11G you could do the same thing but with a normal column, maintained via a trigger.

Answer 3

SELECT * FROM table WHERE REGEXP_LIKE(col1, <pattern>)
union
SELECT * FROM table WHERE REGEXP_LIKE(col2, <pattern>)
union
SELECT * FROM table WHERE REGEXP_LIKE(col3, <pattern>)

this should work. but i doubt if this would be any better in performance than your query. you might want to compare the performances of both. would really love to hear from you on your findings. :-)

Answer 4

Have you thought about using the Concatenated Datastore feature of Oracle Text?

Oracle Text lets you create full-text indexes on multiple columns in the same table. Or at least there's a process by which you can do this.

There's a good example document on the Oracle site I've used before:

http://www.oracle.com/technology/sample_code/products/text/htdocs/concatenated_text_datastore/cdstore_readme.html

Oracle Text searches are ridiculously fast. I think I'd look at keeping separate context indexes on each individual column so that you could apply relevance and priority to each column match.

Let me know if you'd like an example and I'll add something to the answer.

Hope this helps.