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?
 
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
 
awking00Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
d27m11yAuthor Commented:
Thank you.. let me get back to you on this..
0
 
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
 
awking00Commented:
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
 
awking00Commented:
Sorry, sdstuber, didn't see your post before I submitted. It's nice to see that we agree though :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.