Search Test String for Column Values

Analytics
Enthusiast

Search Test String for Column Values

I need to search a VARCHAR(256) column for values stored in another table. Any thoughts?

Something like this:

select text_string
from table1
where text_string like any (select empnum from table2)

Note that table2 has something like 4MM rows...

BobL
2 REPLIES
Senior Apprentice

Re: Search Test String for Column Values

Hi Bob,
your syntax is already close:

select text_string
from table1
where text_string like any (select '%' || trim(empnum) || '%' from table2)

But of course this will result in a CROSS JOIN.

But the main question is:
Why do you need to run that stupid query? The data model seems to be quite bad.

Dieter
Enthusiast

Re: Search Test String for Column Values

The business partner is looking for employee numbers that are part of a comment. Stupid, yes; necessary, yes!

Thanks. I'll try this out.