use regexp_like with multiple values

W D
W D used Ask the Experts™
on
Hi, I have the following SQL (we use Oracle) in my Crystal Reports command object:

WITH
 full_narr AS(
SELECT
 it.order_proc_id
, it.line
, it.narrative  narr_impression
FROM impression_text it
WHERE regexp_like(it.narrative,'{?Keyword}','i')
)

How can I use regexp_like to allow multiple values? Do I need to write multiple OR statements?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
when you say multiple values, do you mean you want multiple key words to be found?

if so,  use the regexp "or" operator "|"

where regexp_like(narrative,'first|second|third|fourth','i')
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You can use the pipe (|) in regex for OR:

Try:
WHERE regexp_like(it.narrative,'{?Keyword}|{?Keyword2}','i')
one option is to use the pipe (or) operator in your regex

for example:


WHERE regexp_like(it.narrative,'{?Keyword1}|{?Keyword2}|{?Keyword3}','i')
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

W DData Analyst

Author

Commented:
Wow! Thanks for all of the promp replies!! Er, yes, sdstuber, I would like multiple key words to be found. Didn't know one could use the pipe operator. Since you were first, sdstuber, I will award points to you. Many, many thanks to both slightwv and mankowitz for your comments.
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
SQL does not allow multiple value input parameters.  That being said, if you have Crystal Reports 2008 or 2011 then you can use multiple value input parameters with SQL Commands, because SAP has basically built in functionality that converts the list of values selected by the user into an array of values that SQL can read.

If you're using XI R2 or lower, please see this example report I've written that demonstrates how to use Multiple-value parameters with SQL Command objects:

https://www.box.net/shared/663ziq8unr
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad to help, both slightwv and mankowitz were within a couple minutes though, close enough that I'm willing to call them all effectively "simultaneous".  I'm ok with splitting the points with them
W DData Analyst

Author

Commented:
Re: I'm ok with splitting the points with them
ah, ok, sdstuber, that's very kind of you.
Since the points are already awarded, I'll contact the Expert's Exchange editors to have them update the points.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
sdstuber and myself are both Zone Advisors and can unaccept this if you wish.

Personally, I'm OK either way.  Just let us know your wishes.
W DData Analyst

Author

Commented:
Re: slightwv and I are both Zone Advisors
Ah, good to know! I was just trolling around the site trying to figure out how to contact customer service. thanks for reopening the question for me again, sdstuber.

Regards,
w delaney
Most Valuable Expert 2011
Top Expert 2012

Commented:
no problem,  but, if you ever do need to get help on a question scroll to the top,  just under the question window itself there is a "Request Attention" link.

That will open a thread with the moderators to assist you.
W DData Analyst

Author

Commented:
Thanks, sdstuber!! :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial