Select the rows that contain Input value

Query to select the rows that have give input value in any one of the column.

Let’s say suppose you have the table naming with TEMP_TBL with the following columns. ID, COLW, COLX, COLY, COLZ.

CREATE TABLE TEMP_TBL
(
ID NUMBER,
COLW VARCHAR2(1),
COLX VARCHAR2(1),
COLY VARCHAR2(1),
COLZ VARCHAR2(1)
);

Let’s say the table is populated with the following data .

Query to insert the data into the table.

INSERT ALL
INTO TEMP_TBL(ID,COLW,COLX,COLY,COLZ) VALUES(1,'P','Q','R','S')
INTO TEMP_TBL(ID,COLW,COLX,COLY,COLZ) VALUES(2,NULL,'P','Q','R')
INTO TEMP_TBL(ID,COLW,COLX,COLY,COLZ) VALUES(3,'S',NULL,NULL,'P')
INTO TEMP_TBL(ID,COLW,COLX,COLY,COLZ) VALUES(4,'Q','S','R',NULL)
SELECT * FROM DUAL ;

Query to select the Result from Table

SELECT * FROM TEMP_TBL;

Question

Write a query to select the rows that have “P” in any one of the column?

Solution:

We can select the rows that have “P” in any one of the column in multiple ways using OR,ANY, SOME, IN operators,

Query 1 using OR operator

SELECT * FROM TEMP_TBL WHERE COLW='P' OR colX='P' OR COLY='P' OR COLZ='P';

Query 2 using ANY operator

SELECT * FROM TEMP_TBL WHERE 'P' =ANY (COLW, colX, COLY, COLZ);

Query 3 using SOME operator

SELECT * FROM TEMP_TBL WHERE 'P' =SOME (COLW, colX, COLY, COLZ);

Query 4  using IN operator

SELECT * FROM TEMP_TBL WHERE 'P' IN (COLW, colX, COLY, COLZ);

Query 5  using UNION operator

SELECT * FROM TEMP_TBL WHERE COLW='P'
UNION
SELECT * FROM TEMP_TBL WHERE COLX='P'
UNION
SELECT * FROM TEMP_TBL WHERE COLY='P'
UNION
SELECT * FROM TEMP_TBL WHERE COLZ='P';

Query 5  using UNION ALL operator

SELECT * FROM TEMP_TBL WHERE COLW='P'
UNION ALL
SELECT * FROM TEMP_TBL WHERE COLX='P'
UNION ALL
SELECT * FROM TEMP_TBL WHERE COLY='P'
UNION ALL
SELECT * FROM TEMP_TBL WHERE COLZ='P';

Query 6  using LIKE operator

SELECT * FROM TEMP_TBL WHERE (COLW||colX|| COLY|| COLZ) LIKE '%P%';

Query 6  using INSTR operator

SELECT * FROM TEMP_TBL WHERE INSTR(COLW||colX|| COLY|| COLZ,'P') > 0;

Below is the output for all the above queries

Output