Solved

Regexp to get exact word

Posted on 2015-02-18
12
126 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
  • 7
  • 4
12 Comments
 
LVL 76

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 73

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
 
LVL 73

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 73

Expert Comment

by:sdstuber
ID: 40617528
I reread the query I think I understand better now I'll try again
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now