regexp like..

I wanted to check comments in app_comments table and find the accounts in app table which has comments that contains 'CSI' or 'manual letter'. 'CSI' or 'manual letter' can be case-insensitive. Can we check this using regexp. Please advise.

SELECT c.app_ref_no, d.corp_acct_no, d.indv_acct_no

, d.row_insert_ts, c.cmnt_tp_cd_desc, c.comment_seq_no, c.cmnt_tx 

FROM app_onln_cmnt c

, app_dcsn d

WHERE c.app_ref_no = d.app_ref_no

AND c.row_insert_ts = d.row_insert_ts

AND d.ltr_id = 'No Letter'

AND (UPPER(c.cmnt_tx) like '% CSI %' OR UPPER(c.cmnt_tx) like '%MANUAL%' )

AND c.row_insert_ts = '29-APR-2015'

ORDER BY 1,2,3,4,6

Open in new window



Immediate reply is appreciated !
d27m11yAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
You can using the following:
where regexp_like(c.cmnt_tx,'(CSI|manual)','i');
0
sdstuberCommented:
what you had

(UPPER(c.cmnt_tx) like '% CSI %' OR UPPER(c.cmnt_tx) like '%MANUAL%')

Open in new window


should be more efficient than using regular expressions. You might want to remove the spaces around the "CSI", depending on the nature of the text.   Or, if spaces are appropriate, then you may want to include them for "MANUAL"  which maybe should be "MANUAL LETTER"

Also, if spaces are needed, then they should be reflected in the regular expression as well

regexp parsing is powerful, but also an expensive operation,  if you don't need to use it then you probably shouldn't.
0
d27m11yAuthor Commented:
Thank you.. let me get back to you on this..
0
Determine the Perfect Price for Your IT Services

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

d27m11yAuthor Commented:
Actually, I am looking at the either of these and they could be either uppercase or lowercase.

Manual
MNL
Man
Letter
LTR
LTTR
CSI

How can I trim the spaces around these. Can I use regexp like

 (regexp_like(c.CMT_TX,'(mnl|manual|man)','i')  
or
regexp_like(c.CMT_TX,'(Letter|LTR|LTTR)','i')
or
regexp_like(c.CMT_TX,'(CSI)','i') )

or should I use

regexp_like(c.CMT_TX,'(mnl|manual|man|Letter|LTR|LTTR|CSI)','i')
0
sdstuberCommented:
don't put the expression in () if you don't need to

also checking for MANUAL isn't needed since MAN is sufficient

regexp_like(c.CMT_TX,'mnl|man|Letter|LTR|LTTR|CSI','i')

but it would probably still be more efficient to use

UPPER(c.cmt_tx) like '%MNL%'
or UPPER(c.cmt_tx) like '%MAN%'
or UPPER(c.cmt_tx) like '%LETTER%'
or UPPER(c.cmt_tx) like '%LTR%'
or UPPER(c.cmt_tx) like '%LTTR%'
or UPPER(c.cmt_tx) like '%CSI%'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
The combined OR list should work, although I would remove the 'manual' choice since it's covered by the 'man' choice. However, as sdstuber pointed out, regular expression are expensive so you may consider using where upper(cmt_tx) like '%CMI%' or upper(cmt_tx) like '%MAN%' or upper(cmt_tx) like '%MNL%' or upper(cmt_tx) like '%LTR' or upper(cmt_tx) like '%LTTR%' or upper(cmt_tx) like '%LETTER%'. You might try setting autotrace traceonly and run the query for both methods to compare.
0
awking00Information Technology SpecialistCommented:
Sorry, sdstuber, didn't see your post before I submitted. It's nice to see that we agree though :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.