Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3406
  • Last Modified:

need to understand regexp_substr and connect by level Oracle

followup from a previous post:

I'm studying the solution (posted below), some questions . . .

so this
TABLE(
                         SELECT COLLECT(LEVEL)
                           FROM DUAL
                     CONNECT BY LEVEL <= LENGTH(x.stripped_mtg_time) - LENGTH(REPLACE(x.stripped_mtg_time, ',')) + 1
                 ) 

Open in new window

returns
1
2
for T1,TR567?

I kind of see that from this simple example:
select level,t.*
from 
( select 'one' AS VALUE from dual
  union all
  select 'two'from dual
) t
connect by level<=2
ORDER BY  VALUE, LEVEL

Open in new window


But where does COLUMN_VALUE come from (see solution below) ? Is it looking for the pattern in the COLUMN_VALUE occurrence ? so it evaluates 1,2 looking for the pattern (F1 inside of M1,F12) ?

previous post solution:
 SELECT a,
         b,
         MAX(
             CASE
                 WHEN INSTR(sch, b_day) = 0 THEN 'bad'
                 WHEN b_period = '1' AND NOT REGEXP_LIKE(sch, '1([^0]|$)') THEN 'bad'
                 WHEN INSTR(sch, b_period) > 0 THEN 'good'
                 ELSE 'bad'
             END
         ) AS new_method
    FROM (SELECT a,
                 b,
                 REGEXP_SUBSTR(
                     a,
                     '[^,]+',
                     1,
                     COLUMN_VALUE
                 )
                     sch,
                 REGEXP_SUBSTR(b, '^[MTWRFS]') b_day,
                 REGEXP_SUBSTR(b, '[0-9]+$') b_period
            FROM tab1 x,
                 TABLE(
                         SELECT COLLECT(LEVEL)
                           FROM DUAL
                     CONNECT BY LEVEL <= LENGTH(x.a) - LENGTH(REPLACE(x.a, ',')) + 1
                 ))
WHERE a IN ('M1,F12','MW34,T56,R12,F1234') -- testing !!!
GROUP BY a, b 
-- ORDER BY a, b 
ORDER BY a, CASE SUBSTR(b,1,1) WHEN 'M' THEN 1 WHEN 'T' THEN 2 WHEN 'W' THEN 3 WHEN 'R' THEN 4 WHEN 'F' THEN 5 WHEN 'S' THEN 6 END, SUBSTR(b,2)

Open in new window

which produces this
M1,F12	M1	good
M1,F12	F1	good
M1,F12	F2	good
MW34,T56,R12,F1234	M3	good
MW34,T56,R12,F1234	M4	good
MW34,T56,R12,F1234	T5	good
MW34,T56,R12,F1234	T6	good
MW34,T56,R12,F1234	W3	good
MW34,T56,R12,F1234	W4	good
MW34,T56,R12,F1234	R1	good
MW34,T56,R12,F1234	R2	good
MW34,T56,R12,F1234	F1	good
MW34,T56,R12,F1234	F2	good
MW34,T56,R12,F1234	F3	good
MW34,T56,R12,F1234	F4	good

Open in new window

0
Gadsden Consulting
Asked:
Gadsden Consulting
  • 8
  • 6
  • 2
2 Solutions
 
sdstuberCommented:
the TABLE() function creates COLUMN_VALUE.


It has to give a name to values pulled from the collection.

If the collection were to be cast as some named collection type, the TABLE function would return the names of the elements in those types.   Since it's just a generic collection, it generates its own name.


It has nothing to do with the regexps or the connect by, it's solely an artifact of the TABLE() function
0
 
sdstuberCommented:
Here's a simpler example to show the column_value generation

select * from table(select collect(level) from dual connect by level < 10)
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>the TABLE() function creates COLUMN_VALUE, etc.
-- ok, that helps and makes sense.

>>select * from table(select collect(level) from dual connect by level < 10)
-- ok, I'll check this tomorrow and probably have some additional questions.

Thx.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>the TABLE() function creates COLUMN_VALUE, etc.
>>Here's a simpler example to show the column_value generation: select * from table(select collect(level) from dual connect by level < 10)
-- ok, I see both of these, thx.

-----------------
for the 1st REGEXP_SUBSTR here:
SELECT a,
                 b,
                 REGEXP_SUBSTR(
                     a,
                     '[^,]+',
                     1,
                     COLUMN_VALUE
                 ) sch,
                 REGEXP_SUBSTR(b, '^[MTWRFS]') b_day,
                 REGEXP_SUBSTR(b, '[0-9]+$') b_period
            FROM tab1 x,
                 TABLE(
                         SELECT COLLECT(LEVEL)
                           FROM DUAL
                     CONNECT BY LEVEL <= LENGTH(x.a) - LENGTH(REPLACE(x.a, ',')) + 1
                 )

Open in new window


- I see the results, but not quite getting the REGEXP_SUBSTR . . .
REGEXP_SUBSTR(
                     a,
                     '[^,]+',
                     1,
                     COLUMN_VALUE
                 )

so this is saying
- look at "a" (M1,R12),
- for pattern "not comma, then everything after the comma"
- start in position 1,
- looking for the COLUMN_VALUE occurrence

but I'm just not seeing how this results in M1 for the 1st occurrence and R12 for the second occurence - I don't see the mechanics . . .

A	B	SCH	Dy	Pd	COLUMN_VALUE
M1,R12	M1	M1	M	1	1
M1,R12	R1	M1	R	1	1
M1,R12	R2	M1	R	2	1
M1,R12	M1	R12	M	1	2
M1,R12	R1	R12	R	1	2
M1,R12	R2	R12	R	2	2
MWF4	M4	MWF4	M	4	1
MWF4	W4	MWF4	W	4	1
MWF4	F4	MWF4	F	4	1

Open in new window

0
 
sdstuberCommented:
>>  for pattern "not comma, then everything after the comma"

no

it's saying look for substrings consisting of 1 or more not-commas


The "+" means 1 or more of whatever expression came immediately before it,
which was  not-commas
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
>>it's saying look for substrings consisting of 1 or more not-commas
>>The "+" means 1 or more of whatever expression came immediately before it, which was  not-commas
-- so it must be looking at it as "M1 R12" and says, "oh look, there's two strings there, the first occurrence being M1 and the second being R12"
0
 
sdstuberCommented:
yes exactly, that's why the COLUMN_VALUE is generated so we can iterate through chunks of non-comma substrings.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
Ok, I think I see it all now, thanks a lot.

For
TABLE(SELECT COLLECT(LEVEL) FROM DUAL CONNECT BY LEVEL <= LENGTH(x.a) - LENGTH(REPLACE(x.a, ',')) + 1

does this all fall under the heading "Collections" and so I need to study up on that ?
0
 
sdstuberCommented:
yes

COLLECT() as the name implies, creates a collection of NUMBERs
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
sorry, one more quick question . . .

slightwv posted this book for REGEXP. The reviews look good, but 544 pages for REGEXP ? I already have a 1,300 page PL/SQL book by Feuerstein to slog through . . .
0
 
slightwv (䄆 Netminder) Commented:
>> but 544 pages for REGEXP ?

Powerful doesn't come easy...  That book will take you places with regex that most people outside of Perl programmers never dare to go.

The regex covered in this question was pretty simple compared to some expressions that are out there.

Remember that that book isn't 100% compatible with Oracle's implementation of regex.

A lot of regex (and SQL/programming/etc... for that matter) is just understanding the concepts of "what is possible" and not memorization.
0
 
sdstuberCommented:
Read the oracle sql guide.  It's appendix is much smaller than 544 and is oracle specific.

Then, simply practice. - this is more important than any resource either of us mentioned.

Try a little regexp golf for fun.  Note Oracle's implementation might not be able to solve patterns that others can.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
slightwv,

ok, thanks. At the moment I am focused on Oracle, so the book is probably out of scope for me. I did run into this in some JavaScript, but still 544 pages is probably more than I need.

I have a 11g Sql book by Jason Price. It doesn't have detailed explanations of REGEXP, so I kind of need something between that and 544 pages of not really Oracle stuff.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
sdstuber,

>>Read the oracle sql guide.
-- ok, sounds good.

>>Then, simply practice
-- I broke everything down in the excellent solution I got, so It's starting to make sense, and I'm sure I'll have other opportunities to excel . . .
0
 
slightwv (䄆 Netminder) Commented:
>>At the moment I am focused on Oracle, so the book is probably out of scope for me.

If I had to guess I would say the book is 80-90% accurate for Oracle's implementation but I last touched my copy over 15 years ago.
0
 
Gadsden ConsultingIT SpecialistAuthor Commented:
slightwv, ok, I will take that into consideration, thx.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now