Solved

Regexp to get exact word

Posted on 2015-02-18
12
134 Views
Last Modified: 2015-02-20
I am trying to identify the rows which match the below conditions from the Source String

Source Strings
1) Match
Should get selected.
2) Matched
Should not get selected as it has "ed" at the end.
3)  Match
Should get selected even though there is a space before the keyword.
4) RO_Match
Should not get selected as the word is not exactly same as "Match"
6) RMatch
Should not get selected as the word is not exactly same as "Match"
7).Match
Should get selected as  "." character is allowed before the  Keyword.

How to do this thru regexp_instr?
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40617339
Do you have a list of 'allowed' and 'not allowed' characters?

For example:  '_' isn't allowed but '.' is.

Given the explanations above:
RO.Match
Should get selected
0
 

Author Comment

by:premkumar somasundaram palani
ID: 40617399
Allowed characters
1) .  (Period)- Zero or one occurence
2) Space - Zero or more occurence

Not allowed
1) Any alphanumeric character
2)  Alphanumberic in combination with Underscore. Ex: RO_Match
3) Only Underscore

RO.Match is not allowed as it has "RO" before "."

I am trying to pick the database object names from Source String.

Database objects list:
1) RO_MATCH
2) MATCH
3) TEST_Package

Source String is "XXXX Match XXXX Test_Package".
Only Match and Test_Package should be selected. But this query regexp_instr(<Source String>,'[^_]*'||dba_objects.object_name||'[^_]*) gives RO_Match in the result.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40617445
how about this?

regexp_instr(source_string,' *.?Match.? *)

or maybe simpler but not completely functionally equivalent


regexp_instr(source_string,'[ .]Match[ .]')
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40617451
A full solution is going to depend on the search space. (i.e. all source strings)

There are expressions that can match your rules for some strings but not others.
0
 

Author Comment

by:premkumar somasundaram palani
ID: 40617476
regexp_instr(source_string,' *.?Match.? *)  ==> still prints RO_MATCH in output
regexp_instr(source_string,'[ .]Match[ .]') ==> NO Values are getting printed out.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40617528
I reread the query I think I understand better now I'll try again
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40617570
SELECT REGEXP_INSTR(
           'XXXX Match XXXX Test_Package',
           '(^|[. ])'||object_name||'([. ]|$)',
           1,
           1,
           0,
           'i'
       )
  FROM (SELECT 'RO_MATCH' object_name FROM DUAL
        UNION ALL
        SELECT 'MATCH' FROM DUAL
        UNION ALL
        SELECT 'TEST_Package' FROM DUAL)


you would use your real query from dba_objects instead of the inline view I used
0
 

Author Comment

by:premkumar somasundaram palani
ID: 40617701
When i substitute the values in Your query, it gives me correct result. But my query does not print "Match".

My Exact Source String:  "INSERT INTO R_MATCH SELECT xxxx FROM  TEST_PACKAGE"

Query:  regexp_instr(TM.SQL_STMT,'(^|[. ])'||do.object_name||'([. ]|$)')

If i use the query regexp_instr(TM.SQL_STMT,'[^_]*'||do.object_name||'[^_]*')    both R_Match and Match gets printed. I need only R_Match in the output.

Since my source string is multi-lined, not sure this would be the reason.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40618763
>>> But my query does not print "Match".

why would it?  you asked to use regexp_instr which returns a number


>>> Query:  regexp_instr(TM.SQL_STMT,'(^|[. ])'||do.object_name||'([. ]|$)')

that's not a query, that's a function call
please post your query that you are using where you are trying to "print" these results
 
>> If i use the query regexp_instr(TM.SQL_STMT,'[^_]*'||do.object_name||'[^_]*')  

I already posted a different expression an hour prior to your reply, try that one


>>> My Exact Source String:  "INSERT INTO R_MATCH SELECT xxxx FROM  TEST_PACKAGE"
>>> Since my source string is multi-lined, not sure this would be the reason.


These two statements contradict each other.

Is the first line really the source string, or is the second line correct and you have a multi-line statement?
If the latter, then please provide a real example.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40619032
you have been mixing case in your examples and explanations with contradictory results

also,  this example doesn't match previous examples

If i use the query regexp_instr(TM.SQL_STMT,'[^_]*'||do.object_name||'[^_]*')    both R_Match and Match gets printed. I need only R_Match in the output.

Why would R_Match   be found when your inputs  are

Database objects list:
1) RO_MATCH
2) MATCH
3) TEST_Package

TEST_Package should match but none of the others should using the last expression I posted.
However it won't match either if you want case-sensitive matching, which, as noted above, you seem to have vacillated on.

Your question will probably be easy to answer if you can provide a complete, representative and consistent test case of sample data and expected results (both in rows and columns, not descriptions.)
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40619073
Here's an example of a test case of input data and expected results

WITH source_strings
     AS (SELECT 'Match' source_string FROM DUAL
         UNION ALL
         SELECT 'Matched' FROM DUAL
         UNION ALL
         SELECT '  Match' FROM DUAL
         UNION ALL
         SELECT 'RO_Match' FROM DUAL
         UNION ALL
         SELECT 'RMatch' FROM DUAL
         UNION ALL
         SELECT '.Match' FROM DUAL
         UNION ALL
         SELECT 'XXXX Match XXXX Test_Package' FROM DUAL
         UNION ALL
         SELECT 'INSERT INTO R_MATCH SELECT xxxx FROM  TEST_PACKAGE' FROM DUAL
         UNION ALL
         SELECT 'INSERT INTO R_MATCH SELECT xxxx
FROM  TEST_PACKAGE' FROM DUAL),
     my_objects
     AS (SELECT 'RO_MATCH' object_name FROM DUAL
         UNION ALL
         SELECT 'MATCH' FROM DUAL
         UNION ALL
         SELECT 'TEST_Package' FROM DUAL)



Expected Results:


OBJECT_NAME     SOURCE_STRING                                      
------------    ---------------------------------------------------
RO_MATCH        RO_Match                                           
MATCH           Match                                              
MATCH             Match                                            
MATCH           .Match                                             
MATCH           XXXX Match XXXX Test_Package                       
TEST_Package    XXXX Match XXXX Test_Package                       
TEST_Package    INSERT INTO R_MATCH SELECT xxxx FROM  TEST_PACKAGE 
TEST_Package    INSERT INTO R_MATCH SELECT xxxx 
                FROM  TEST_PACKAGE

Open in new window



Change/extend the input data as needed to be representative of your real source strings and objects

Change the expected results to be whatever you are expecting the query to return.
0
 

Author Comment

by:premkumar somasundaram palani
ID: 40621348
Your last solution it worked.I used instr to check if such a object is present and substr to extract the same.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question