Find a string in a specific table

Question

I'm working with a oracle database, I'm wondering if there is a way to find all rows that contains a value in any column. For example let's consider this table:

               Weather

city          state        high    low

Phoenix       Arizona      105     90

Tucson        Arizona      101     92

Flagstaff     Arizona      88      69

San Diego     California   77      60

Albuquerque   New Mexico   80      72

Basically (I know it's not possible), but I would like to do something like this:

Select * From Weather Where * LIKE '%f%'

and it would give me the rows

Flagstaff     Arizona      88      69
San Diego     California   77      60

I could do it on the Java side, by querying all the rows then with the ResultSet dynamically search for a given value in a column and add that row. The problem is that some table contains millions of rows and I guess it would be more efficient do it on the database side, so I only fetch wanted rows from the network.

Is it possible to do it on the SQL side directly?

@AniketThakur No only the value, otherwise it's just a simple sql query with a where clause — user2336315, Apr 25 '15 at 17:10
You need to know which table user is trying to query. That way you could get all of it's columns in a function and then build a query with `where` condition on every column. — Kamil Gosciminski, Apr 25 '15 at 17:13
If you are not sure about the column name in advance, then obviously you cannot have a static query. So, the only possible way is to (ab)use dynamic SQL in PL/SQL. — Lalit Kumar B, Apr 25 '15 at 17:14
you may refer to this :[ MS-SQL][1] [1]: http://stackoverflow.com/questions/709120/how-to-search-for-one-value-in-any-column-of-any-table-inside-one-ms-sql-databas — Chirag Kamat, Apr 25 '15 at 17:15
@ConsiderMe Actually performance doesn't really matter at first, the only consideration is to fetch only interesting rows (so to not transfer useless data over the network) — user2336315, Apr 25 '15 at 17:19

score 3 · Accepted Answer · answered Apr 25 '15 at 17:16

You can use view ALL_TAB_COLUMNS to search all columns in given table:

DECLARE
  v_table_name VARCHAR2(30) := 'dual';
  v_search_string VARCHAR2(100) := 'X';
  v_sql VARCHAR2(4000);
  c_result SYS_REFCURSOR;
BEGIN
  v_sql := 'SELECT * FROM ' || v_table_name || ' WHERE 1=1';
  FOR r_c IN (SELECT column_name
              FROM all_tab_columns
              WHERE table_name = v_table_name) LOOP
    v_sql := v_sql || ' OR ' || r_c.column_name || ' LIKE ''%' || v_search_string || '%''';
  END LOOP;
  OPEN c_result FOR v_sql;
END;
/

In this case you can write this as procedure and return cursor to JAVA to fetch data.

Only - all time you will face table FULL scans. But if you look at data transfer then this solution will be better than to do all search in JAVA.

Lalit Kumar B · Answer 2 · 2015-04-25T17:24:01.927

Is it possible to do it on the SQL side directly?

No. It is not possible in pure SQL since you need to know the column names in advance, i.e. they must be static.

The only possible way to build the query dynamically is to (ab)use EXECUTE IMMEDIATE in PL/SQL.

Though, I would say it is not a good design. The application must know which objects to access in advance.

Anyway, in PL/SQL you could do something like:

v_sql = q'[SELECT ... FROM table_name WHERE ]';
v_sql:= v_sql || column_name1 || ' LIKE ''%f''' || ' OR '|| column_name2 || 'LIKE ''%f''';

And then, use EXECUTE IMMEDIATE to execute the dynamic sql.

score 0 · Answer 3 · answered Apr 25 '15 at 18:19

You have to read thru "Text Application Developer's Guide" http://docs.oracle.com/cd/B28359_01/text.111/b28303/query.htm#g1016054

May be you can concatenate all your data column and update in one more addition column in the same table, then use text search to get the result.

Find a string in a specific table

3 Answers3