Solved

Regexp to get exact word

Posted on 2015-02-18
12
135 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

630 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