CODEWITHSUNDEEP
sqloracle-database
Sandeep Reddy
Sandeep Reddy

Reputation: 3

How to write a SQL query to search all the columns?

select *
from DEMO_ORDER_ITEMS
where (
    ORDER_ITEM_ID like '%1%' || 
    ORDER_ID like '%1%' ||
    PRODUCT_ID like '%1%' ||
    UNIT_PRICE like '%1%'
);

Is this the correct query to search and fetch the searched record from database from all the columns?

Upvotes: 0

Views: 51

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You probably need an OR:

select *
    from DEMO_ORDER_ITEMS
    where (
           ORDER_ITEM_ID like '%1%' OR
           ORDER_ID      like '%1%' OR
           PRODUCT_ID    like '%1%' OR
           UNIT_PRICE    like '%1%'
          );

Please notice that the parentheses are not necessary here; however, as Matthew McPeak noticed, they give no issue and can be useful in case you need to add some other condition in AND to the existing OR conditions.

The || is the operator for concatenation in Oracle; for example:

SQL> select 'a' || 'b' from dual;

'A
--
ab

Upvotes: 2

Related Questions