Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Regexp to get exact word

Posted on 2015-02-18
12
Medium Priority
?
136 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

661 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