Desktop productivity for business analysts and programmers

SAS EG 4.1 SQL Help - REGEXP_LIKE

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

SAS EG 4.1 SQL Help - REGEXP_LIKE

Hello Everyone!

Need help... i have a query that i have made in SAS EG 4.1 in SQL... the premise is that i am looking to search through a free text field for keywords.  My question is two prong.

     1.) When searching, I am looking to ignore the case of the text.  So no matter what the user types in, say "helium"... it will not look for upper case or lower case... it will return all the results with that word regardless of case.  I have tried about 30 different combinations that i have found online... but none work.  So, the code i have posted i know for sure works but will return everything in that particular case... so i need help figuring out that one.

     2.) I am setting up another project that is nearly identifcal, but uses 4 criteria search methods that will all be 'or'... what is the best way to code that?  I have tried in the past and have failed... so before i get going on that one... i would like some advice from the communiity.  I have my symput statements on top for start and end dates.. i jiust didn't include them, nor the oracle connection settings... I have only included the main query starting with select statement.

Any help is oh so much appreciated.

SELECT/*+ INDEX (AO ODR_PK) USE_NL(AO)  */


distinct AM.DT_CRTD,




as ORIGINAL_MESSAGE,





FROM  ATS_ORDERS             AO,




WHERE AM.DT_CRTD >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))


AND   AM.DT_CRTD <  trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1)


AND   REGEXP_LIKE (AM.MESSAGE_TEXT,(%STR(%'&Search_String%')))


AND   AO.ODR_NUMBER = AM.ODR_ODR_NUMBER


AND  AO.ODR_NUMBER = AD.ODR_ODR_NUMBER


AND   AO.VERSION    = AM.ODR_VERSION


AND   AM.MSG_TYPE IN ('ASK','ANS')


and   AM.DIRECTION = 'I'


AND   AO.Express_Product_Indicator = 'N'


AND   AO.CPY_ABBR <> ALL ('030','042','430','431','549','510','042')






Accepted Solutions
Solution
‎09-24-2012 11:41 PM
Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

Try

REGEXP_LIKE (AM.MESSAGE_TEXT,%unquote(%str(%')&Search_String%str(%')),'i')

View solution in original post


All Replies
Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

Not really sure that I understand what you're after.

REGEXP_LIKE allows for case insensitive search.

"but will return everything in that particular case" What do you mean by that? Are you after selecting rows which satisfy some selection criteria or do you want to extract words from a string (done in the select statement then).

Searching for several key words: You can separate your regex with pipes - that's an OR, eg: (word1|word2|word3)

RegEx is normally used for pattern matching. If you're just searching for an exact (case insensitive) match then a normal LIKE could do as well together with a UPPER() .

Contributor
Posts: 70

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

I am looking to make the regexp_like case insensitive.  I cannot get the syntax correct.

Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

From the Oracle doc: REGEXP_LIKE

WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');

The 'i' is the part which makes the search case insensitive.

Contributor
Posts: 70

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

I added the syntax and it still throws an error:  Here are the Where and "And" lines leading up to the regexp_like

WHERE AM.DT_CRTD >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))

AND   AM.DT_CRTD <  trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1)

AND   REGEXP_LIKE (AM.MESSAGE_TEXT,(%STR(%'&Search_String%','([aeiou])\1', 'i')))

This is probobly something that i am completely overlooking... the error i get is

ORA-00907: missing right parenthesis.

I missed part of your response earlier.  The reason i am using REGEXP_LIKE is because i will have to use this to search for any of the text fields with dollar amounts as well and the dollar amounts vary.  I suppose i could make seperate projects with my line of business, its better to have one project that does various tasks.

Consequently, I tried to figure out a LIKE statement as well.. but still failed at the syntax.  I am pretty new to SQL... however, i am great at googling... and just can't find the syntax with the variable string that i am using in the paremeter.  I think that is tripping me up more than anything.

Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

Please post an example string, the RegEx you're using in the moment and a narrative of what you're looking for in the example string.

Contributor
Posts: 70

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

Let me give you as much background as i can as to what this is all about.  I have a group of clients and one of the methods they use to communicate is a wire type system... kind of a cross between email and instant messaging.  Every message they send is housed in the database.  They use free text to form their messages.   There are many reason to search the text.. but right now i am focused on keywords.  So helium is a keyword that i am using.  Some florists type helium in all caps, some type is with a capitol H and some type the first letter of the word with a capitol.  What I need to do is pull all those messages in at one time so i can see all messages that contain that word.

in this case... the search string is 'helium'  i want to see every text that has the word helium in it.

AND   REGEXP_LIKE (AM.MESSAGE_TEXT,(%STR(%'&Search_String%','([aeiou])\1', 'i')))

Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

REGEXP_LIKE (AM.MESSAGE_TEXT,'helium','i')

or:

upper(AM.MESSAGE_TEXT) like '%HELIUM%'

To get your RegEx right I suggest you first develop and test it using Oracle SQL Developer. Only if you have a working RegEx add it as part of your big pass-through SQL query in SAS.

Contributor
Posts: 70

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

Thanks for that...

REGEXP_LIKE (AM.MESSAGE_TEXT,'helium','i') used works perfectly.

When i add my string variable for my parameter ,

AND   REGEXP_LIKE (AM.MESSAGE_TEXT,(%STR(%'&Search_String%','i'))

that is setup in EG 4.1...  it doesn't work... it throws the

ORA-00907: missing right parenthesis....

The issue is that if i want to give this to any of the leadership and have them simply type in from the parameter pop-up when they run the process.  If they had to go into the SQL to change the string each time... they wouldn't be able to understand how no matter how many times i showed or explained.  Most of our company is very afraid of SQL... LOL.  It's all revolving around the case insensative statement  'i'... if i take it out... the syntax is just fine...

I know this can work... but there is something about the syntax that is not quite right... just can't figure out what it is.   I had not considered that i may need a symput for it... but I don't know why i would need one.. here is the symput data i have just in case.

data_null_;

call symput("Start_Date",put(&START_DATE,date9.));

call symput("End_Date",put(&END_DATE,date9.));

;

Start Date Equal To: &START_DATE;

End Date Equal To: &END_DATE;

In the case that i have a user input parameter setup in EG 4.1... is there anything with the syntax of that statement that i would need to make it work properly?  I am just at a total loss at this point... and have scoured the internet for a similar example without any luck... been searching for 4+ hours on this at this point...

Solution
‎09-24-2012 11:41 PM
Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

Try

REGEXP_LIKE (AM.MESSAGE_TEXT,%unquote(%str(%')&Search_String%str(%')),'i')

Contributor
Posts: 70

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

Thank you so much... now i can clean up the blood from knocking my head against a solid brick wall.  Works perfectly....

Where in the world did you find this?  I searched for hours and never came across anything remotely similar to this... anyway... thank you so much... i think i will wait a while before asking how to add multiple criteria to it... my brain is completely fried.

Thanks again!

Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 SQL Help - REGEXP_LIKE

That %str(%') adds some whitespace character for masking the quotes is one of this hidden things you just need to know (and you do now).

Eventually you could see in the log something, eg. a little "block" which stands for this whitespace character. Sometimes it also helps to open a log file with something like Notepad++ and "suddenly" the hidden stuff becomes visible.

Just as a rule of tumb: If quoting/masking in any way and then weird things happen give %unquote() a go.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 1075 views
  • 0 likes
  • 2 in conversation