Full text search in all columns in Oracle-CodePudding

I was using the following query in PostgreSQL and I investigate if there is any similar for Oracle 19c:

SELECT * FROM table_name 
WHERE table_name::TEXT LIKE '%some_text%';

The best alternative that I can find for Oracle is the following but I am wondering if there is any better approach.

SELECT * FROM table_name
WHERE
   COALESCE(to_char(column1), '') || 
   COALESCE(to_char(column2), '') || 
   COALESCE(to_char(columnN), '') 
LIKE '%some_text%';

CodePudding user response：

Your query will give false positives where half the text is in one column and the second half of the text is in the next column. You can use OR and filter on each column:

SELECT *
FROM   table_name
WHERE  column1 LIKE '%some_text%'
OR     column2 LIKE '%some_text%'
-- ...
OR     columnN LIKE '%some_text%';

Note: you can also skip filtering the non-string columns.

CodePudding user response：

Have a look at regex. Example can be found here. Mastering regex will help if you ever write scripts and so on..