Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Sagar Makhesana 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. :-) –  Sagar Makhesana Sep 26 '13 at 15:11

3 Answers 3

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.

share|improve this answer
    
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. –  Erwin Brandstetter 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. –  Sagar Makhesana 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

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

Select * from Test where instr(col1||col2||col3,'ABC') <> 0;
share|improve this answer
4  
Such query will return row where col1='A' and col2='BC'. –  Mindaugas Riauba Sep 26 '13 at 15:11
    
Yes, you're right, you have to add a separator between each column. –  Nicolas Durand Sep 26 '13 at 15:30
1  
@NicolasDurand: And be certain that the separator cannot be part of the search pattern ... –  Erwin Brandstetter 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? –  Sagar Makhesana 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] –  Nicolas Durand Sep 27 '13 at 7:20

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%'
);
share|improve this answer
    
it looks fine let me try. –  Sagar Makhesana Sep 27 '13 at 9:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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