CODEWITHSUNDEEP
sqloracle-database
rK143
rK143

Reputation: 11

how to search for multiple strings in multiple columns in ORACLE SQL where clause

My situation - I have to two tables with several columns each, and I need to find certain strings in certain columns in those two tables. For example the search string could be 'ExampleString1' , 'ExampleString2%' etc around 20 strings and about 5 - 6 columns in each table.

I m using the following to find atleast one string in the multiple columns, but this even is not working.

select * from table1 a where upper('ExampleString1%') in (a.Column1, a.column2, a.column3)

Although I can do some basic sql queries, I m not that acquaint with sql. I like to know the solution or any material I can study to get to solution.

Thanks rK

Upvotes: 1

Views: 3721

Answers (2)

Sriram Venkat
Sriram Venkat

Reputation: 1

The use of upper() can be avoided here by using REGEXP_LIKE

SELECT * FROM TABLE A WHERE 
REGEXP_LIKE(COLUMN1 || COLUMN2 || COLUMN3, '<search expression>', 'i')

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18737

You can combine all required fields and run a check on that:

select * 
from table1 a
where NVL(upper(a.Column1),'')||NVL(upper(a.column2),'')||NVL(upper(a.column3),'') like upper('ExampleString1%')

Upvotes: 3

Related Questions