Oracle: "grep" across multiple columns?

I would like to perform a li开发者_开发技巧ke 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!

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.

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.

As a consultant I often have to search in poorly documented databases and have the need to have some handy scripts to find data. Here is two examples how to generate a select clause for searching data in all 'VARCHAR2' columns in a table:

Example1. Search part of string:

SELECT 'SELECT * FROM ' || min(TABLE_NAME) ||' WHERE ' || LISTAGG(COLUMN_NAME, ' like ''%somestring%'' or ') WITHIN GROUP (ORDER BY COLUMN_ID) || ' like ''%somestring%'';' 
from ALL_TAB_COLUMNS
WHERE OWNER = 'YOUR_SCHEMA_NAME' -- Uppercase
AND TABLE_NAME = 'YOUR_TABLE_NAME' --Uppercase
AND DATA_TYPE LIKE 'VARCHAR2';

Example2. Search the entire value:

SELECT 'SELECT * FROM ' || min(TABLE_NAME) ||' WHERE ''somestring'' in (' || LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID) || ');' 
from ALL_TAB_COLUMNS
WHERE OWNER = 'YOUR_SCHEMA_NAME' -- Uppercase
AND TABLE_NAME = 'YOUR_TABLE_NAME' --Uppercase
AND DATA_TYPE LIKE 'VARCHAR2';

Does regexp_like help.

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

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. :-)