Solved

need workaround to REGEXP_COUNT Oracle, due to not having 11g

Posted on 2014-10-29
8
306 Views
Last Modified: 2014-10-29
I need a workraound to REGEXP_COUNT, due to not having 11g (we are moving there, but not anytime soon).

I need to know how many times of a pattern. The pattern is straightforward, e.g., M5T34W123R5F567S12, which is
M(Mon) followed by [0-9]+, or T(Tues)+[0-9]+ etc. through S(Sat)+[0-9]+.

I have the basics worked out, and I could work with this in a brute force manner, but thought I'd see if there are any tricks I might be able to use since REGEXP_COUNT isn't available.

WITH tab2 AS 
(SELECT 'M5T34R56' a FROM dual UNION
 SELECT 'M5T34W123R5F567S12' FROM dual UNION
 SELECT 'M56T456W56F56' FROM dual UNION
 SELECT 'T10R9' FROM dual
) 
SELECT DISTINCT a, Regexp_instr(a,'(^[MWTRFS]+[0-9]+$)') AS Matches,
                Regexp_substr(a,'([MWTRFS]+[0-9]+)',1,1) AS "1st",
                Regexp_substr(a,'([MWTRFS]+[0-9]+)',1,2) AS "2nd" ,
                Regexp_substr(a,'([MWTRFS]+[0-9]+)',1,3) AS "3rd",
                Regexp_substr(a,'([MWTRFS]+[0-9]+)',1,4) AS "4th",
                Regexp_substr(a,'([MWTRFS]+[0-9]+)',1,5) AS "5th" ,
                Regexp_substr(a,'([MWTRFS]+[0-9]+)',1,6) AS "6th"                 
                -- Regexp_count(a,'([MWTRFS]+[0-9]+)',1,'i') AS Num_occurances   
FROM tab2                

A	               MATCHES	1st	2nd	3rd	4th	5th	6th
M56T456W56F56	              0	M56	T456	W56	F56		
M5T34R56	              0	M5	T34	R56			
M5T34W123R5F567S12	0	M5	T34	W123	R5	F567	S12
T10R9	                      0	T10	R9				
		

Open in new window


My plan is to count the number of non-null results and use that.
0
Comment
Question by:Gadsden Consulting
[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
  • 4
  • 3
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40411662
WITH tab2 AS
(SELECT 'M5T34R56' a FROM dual UNION
 SELECT 'M5T34W123R5F567S12' FROM dual UNION
 SELECT 'M56T456W56F56' FROM dual UNION
 SELECT 'T10R9' FROM dual
)
select x.*,
   nvl2("1st",1,0) +
   nvl2("2nd",1,0) +
   nvl2("3rd",1,0) +
   nvl2("4th",1,0) +
   nvl2("5th",1,0) +
   nvl2("6th",1,0)  Num_occurances
from
(SELECT DISTINCT a, Regexp_instr(a,'^[MWTRFS]+[0-9]+$') AS Matches,
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,1) AS "1st",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,2) AS "2nd",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,3) AS "3rd",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,4) AS "4th",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,5) AS "5th",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,6) AS "6th"                  
FROM tab2) x
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40411673
note - I took out the extra parentheses from your expressions.

Extra () are not free.  Where the SQL parser will simply ignore extraneous parentheses, the regular expression parser will not.

They create sub-expressions which means an extra level of parsing which isn't needed for yours.  Thus making the execution more expensive.

If you need them use them, if you don't then don't.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 425 total points
ID: 40411686
Another version - this will determine how many day/hour pairs there are even if there are more than 6.

WITH tab2 AS 
(SELECT 'M5T34R56' a FROM dual UNION
 SELECT 'M5T34W123R5F567S12' FROM dual UNION
 SELECT 'M56T456W56F56' FROM dual UNION
 SELECT 'T10R9' FROM dual UNION ALL
SELECT 'M1M2M3M4M5M6M7M8M9M10' from dual
) 
SELECT DISTINCT a, Regexp_instr(a,'^[MWTRFS]+[0-9]+$') AS Matches,
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,1) AS "1st",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,2) AS "2nd",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,3) AS "3rd",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,4) AS "4th",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,5) AS "5th",
                Regexp_substr(a,'[MWTRFS]+[0-9]+',1,6) AS "6th",
                (select max(level) from dual connect by Regexp_substr(a,'[MWTRFS]+[0-9]+',1,level) is not null)Num_occurances                                              
FROM tab2

Open in new window

0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 75 total points
ID: 40411749
Another possible approach could be adapting the workaround shown in this link:

http://oracleappstech-vinoth.blogspot.de/2012/03/regexpinstr-function-for-oracle-9i.html
0
 

Author Comment

by:Gadsden Consulting
ID: 40411804
Thanks sdstuber and Alexander !
I will review later tonight.
0
 

Author Comment

by:Gadsden Consulting
ID: 40412077
sdstuber,

I was able to check at home, and looks great !

good tip on not using extraneous paren's . . .

your second version looks good too, here's what I'll use as my workaround, and then loop through the num occurrences and process the parts.
returns 6 - M5T34W123R5F567S12
SELECT '&mtg_time', Regexp_instr('&mtg_time','^[MWTRFS]+[0-9]+$') AS Matches,
                Regexp_substr('&mtg_time','[MWTRFS]+[0-9]+',1,1) AS "1st",
                Regexp_substr('&mtg_time','[MWTRFS]+[0-9]+',1,2) AS "2nd",
                Regexp_substr('&mtg_time','[MWTRFS]+[0-9]+',1,3) AS "3rd",
                Regexp_substr('&mtg_time','[MWTRFS]+[0-9]+',1,4) AS "4th",
                Regexp_substr('&mtg_time','[MWTRFS]+[0-9]+',1,5) AS "5th",
                Regexp_substr('&mtg_time','[MWTRFS]+[0-9]+',1,6) AS "6th",
                (select max(level) from dual connect by Regexp_substr('&mtg_time','[MWTRFS]+[0-9]+',1,level) is not null) as Num_occurances                                              
FROM dual

Open in new window


Alexander, that's a good link and slick workaround, thx.
0
 

Author Closing Comment

by:Gadsden Consulting
ID: 40412084
excellent, thanks !
0
 

Author Comment

by:Gadsden Consulting
ID: 40412276
Oh, I realize my solution is simpler, i.e.
SELECT '&mtg_time' as mtg_time, (select max(level) from dual connect by Regexp_substr('&mtg_time','[MWTRFS]+[0-9]+',1,level) is not null) as Num_occurances                                              
FROM dual

I don't quite get the level / connect by, but we did that recently so I'll ponder that tomorrow . . . an elegant workaround to not having regexp_count.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 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