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

Question

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

If you're trying to use % as a wildcard, that syntax doesn't work. % works with LIKE, not IN, and it's on the wrong side of the operator anyway. — McGlothlin
– McGlothlin, Commented Jul 7, 2017 at 19:13

Raging Bull · Accepted Answer · 2017-07-07 19:31:04Z

3

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%')

edited Jul 7, 2017 at 19:31

answered Jul 7, 2017 at 19:12

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

McGlothlin Over a year ago

Do you need to use || to concatenate instead? I wasn't aware + would work here.

Jeff Holt Over a year ago

Don't forget to use to_upper on the lhs of the predicate.

McGlothlin Over a year ago

No worries! It said you deleted it... glad you didn't because it was mostly right. One upvote for you.

Raging Bull Over a year ago

@Jacobm001: OP was trying to do the same thing. This parameter could be from user input which OP doesn't have control over.

krokodilko Over a year ago

This query doesn't give desired results (doesn't return a row) where for example a row contains: column1=ABC, column2=ExampleString1, column3=XYZ.

|

Sriram Venkat · Accepted Answer · 2021-04-01 13:42:45Z

0

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')

answered Apr 1, 2021 at 13:42

Sriram Venkat

11 bronze badge

1 Comment

MJar Over a year ago

This query might return rows where columns don't mach the expression that is searched for. eg.: COLUMN1 = "panic", COLUMN2 = "order", search expression = "cord", no column matches the search, but when you concatenate you will get "panicorder" and this row will get incorrectly returned.

Collectives™ on Stack Overflow

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

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related