CODEWITHSUNDEEP
regexoracle-database
user1205746
user1205746

Reputation: 3364

matching patterns for regexp_like in Oracle to include a group of character conditionally

I tried to look up for a good documentation for matching pattern for use of regexp_like in Oracle. I have found some and followed their instructions but looks like I have missed something or the instruction is not comprehensive. Let's look at this example:

SELECT * FROM
(
 SELECT 'ABC' T FROM DUAL
 UNION
 SELECT 'WZY' T FROM DUAL
 UNION
 SELECT 'WZY_' T FROM DUAL
 UNION
 SELECT 'WZYEFG' T FROM DUAL
 UNION
 SELECT 'WZY_EFG' T FROM DUAL
 ) C
 WHERE regexp_like(T, '(^WZY)+[_]{0,1}+[A-Z]{0,6}')

What I expect to receive are WZY and WZY_EFG. But what I got was:

enter image description here

What I would like to have is the "_" could be present or not but if there are character after the first group, it is mandatory that it be present only once.

Is there a clean way to do this?

Upvotes: 0

Views: 80

Answers (1)

Patrick Bacon
Patrick Bacon

Reputation: 4640

Use a subexpression grouping to make sure the _ character appears only with Capitalized Alphabetical Characters

Yes, your pattern does not address the conditional logic you need (only see the _ when capitalized alphabetical characters follow).

Placing the _ character in with a capitalized alphabetical character list into a subexpression grouping forces this logic.

Finally, placing the end of line anchor addresses the zero match scenarios.

SCOTT@DB>SELECT                                         
  2      *                                                  
  3  FROM                                                   
  4      (                                                  
  5          SELECT 'ABC' t FROM dual                       
  6          UNION ALL                                      
  7          SELECT 'WZY' t FROM dual                       
  8          UNION ALL                                      
  9          SELECT 'WZY_' t FROM dual                      
 10          UNION ALL                                      
 11          SELECT 'WZYEFG' t FROM dual                    
 12          UNION ALL                                      
 13          SELECT 'WZY_EFG' t FROM dual                   
 14      ) c                                                
 15  WHERE                                                  
 16      REGEXP_LIKE ( t, '^(WZY)+([_][A-Z]{1,6}){0,1}$' );  
         T                                                  
__________                                                  
WZY                                                         
WZY_EFG

Upvotes: 1

Related Questions