8

I have a table "Test" with 15 different columns of different data types.

I'm looking for rows having characters like "ABC". But "ABC" can be present in any column and I want to find whole records having "ABC".

I cannot restrict the row using a WHERE clause, because I really do not have any idea in which column the ABC resides? Is there a way to do this?

4
  • Which RDBMS? You've tagged both SQL Server and Oracle
    – Philᵀᴹ
    Sep 26 '13 at 14:27
  • Sorry i just tag both . But the question is for ORACLE. Sep 26 '13 at 14:33
  • Lots of ORs, I guess!
    – Philᵀᴹ
    Sep 26 '13 at 15:03
  • yes that is a solution. here in example i had given only 15 column but in my scenario it is near about 50 columns . so if there is any alternative let me suggest. :-) Sep 26 '13 at 15:11
5

Well, there is the plain and simple "brute force" way:

SELECT *
FROM   "Test"
WHERE (col1 LIKE '%ABC%' OR
       col2 LIKE '%ABC%' OR
       col3 LIKE '%ABC%' OR
       ...
       col15 LIKE '%ABC%');

Parentheses won't be necessary if you don't have additional WHERE expressions. Probably faster (and more robust in any case) than concatenating all columns for a single LIKE test.

3
  • 1
    In PostgreSQL, you could just SELECT * FROM tbl t WHERE t::text LIKE '%ABC%. Details in this answer on code review. I don't think you can cast an entire row or record to varchar in Oracle, or at least I would not know about it. Sep 26 '13 at 22:36
  • Yes the query you suggest will be perfactly work but i just wanted to minimize the query.Nice i refer your answer on Code review. actully i m finiding something like that only "SELECT * FROM tbl t WHERE t::text LIKE '%ABC%" will work on it how can i find some similar solution in oracle. Sep 27 '13 at 3:08
  • 1
    Minimise the query definition or minimise the work done when it is run? Because these are different aims.
    – JamesRyan
    Sep 27 '13 at 11:35
2

In Oracle 11g, you can use UNPIVOT

select *
from test unpivot (any_column for source_column in (col1, col2, col3, ...))
where any_column like '%ABC%';

Note that if a row contains ABC in more than one columns, this query returns all of them. If you need the original rows, use a subquery

select *
from test
where id in (
  select id
  from test unpivot (any_column for source_column in (col1, col2, col3, ...))
  where any_column like '%ABC%'
);
1
1

Using the instr built-in function you can do like this:

Select * from Test where instr(col1||col2||col3,'ABC') <> 0;
5
  • 4
    Such query will return row where col1='A' and col2='BC'. Sep 26 '13 at 15:11
  • Yes, you're right, you have to add a separator between each column. Sep 26 '13 at 15:30
  • 1
    @NicolasDurand: And be certain that the separator cannot be part of the search pattern ... Sep 26 '13 at 21:59
  • @Nicolas Instr function is use to find the occurrence of particular character from a given string based on given specification. eg."SELECT INSTR('CORPORATE FLOOR','OR',3,2) "Instring" FROM DUAL;" output---> 14.. So how can we use Column in Instr function? Sep 27 '13 at 5:33
  • The instr function is taking a string as the 1st parameter, so column are accepted, Oracle will cast the column to a string if the column type is not: [techonthenet.com/oracle/functions/instr.php] Sep 27 '13 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.