need help with REGEXP_SUBSTR or REGEXP_INSTR

I need some help understanding REGEXP_INSTR / SUBSTR. I got excellent assistance here, and now I want to learn how to do it myself . . .

The pattern I'm looking for is this: M6T6W6R6, where the numbers are interspersed among the letters
- M = Mon, T = Tues, R = Thurs, etc. The numbers are class periods (1 - 10).

So I want to flag the above as "bad".

but these are good, because the numbers (periods) are not mixed in among the letters (days)
- MR567
- MTRF123456
- MF123

if this can be done, give me a tiny hint and then see if I can get it.
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you need a pattern of letters '[MTWRF]+' followed by numbers '[0-9]+'

to ensure you don't have repeats, then use begin/end markers for the line.

That is, '^'  means the beginning of the line,  '$' means the end of the line.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gadsden ConsultingIT SpecialistAuthor Commented:
ok, thanks, I'll give it a try . . .
0
sdstuberCommented:
note, my notes were for identification of "good" records via something like   regexp_like(.......)

to find bad records you would do NOT regexp_like(.......)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
if you want to find the specific pattern of repeated  letter/number pairs, you could do that too, but differently.

Either looking for letter,number,letter,number  or    letter,number 2 or more times
0
Gadsden ConsultingIT SpecialistAuthor Commented:
well, look at that . . .
SELECT DISTINCT a, Regexp_instr(a,'(^[MWTRFS]+[0-9]+$)') AS Valid_Mtg_Time
FROM tab1
WHERE INSTR(a,',') = 0
   AND a IN ('M5T34R56','WF567','MWF')  

results

WF567	1
MWF	0
M5T34R56	0

Open in new window


great !
0
Gadsden ConsultingIT SpecialistAuthor Commented:
sdstuber,

thanks for the other tips.

I need to study the book to see if I can see where they're telling me what you indicated. I just played around with previous pattern matches you you and slightwv posted here, until it worked. So I want to make sure I can turn to the reference and find the solution.
0
sdstuberCommented:
appendix D of the 11gR2  SQL reference covers the Oracle regexp syntax.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_posix.htm#SQLRF020


it's a shorter read than other books, and it's oracle specific.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
sdstuber,

the Appendix D link I don't think has what I'm looking for . . . I didn't see anything actually describing how to use REGEXP.

I found this which is closer to what I was looking for, although I would never have been able to solve this problem by reading these pages.

In Jason Price's "Oracle Database 11g SQL", he says
" '^' matches the beginning of a string ... and $ matches the end position of a string" whereas you said
" '^'  means the beginning of the line,  '$' means the end of the line"

- is that the same ?

Either way, I would have never been able to figure this out on my own - it doesn't "click".
0
sdstuberCommented:
>> - is that the same ?

unfortunately I have to say "it depends"

Oracle's regexp parser can support multi-line strings.

For example:

"This string
is on
three lines"

The parser can read that as one string of text, or as a string of three lines by recognizing the line breaks.

Thus  ^$ will either refer to the begin and end of the entire string in the former case
or  ^$ will refer to the begin and end of a single line within the string in the latter case.


>>> Either way, I would have never been able to figure this out on my own - it doesn't "click".

regexp's are tricky.  There often multiple ways to write the same thing.  I already gave one difference above with the regexp_like vs NOT regexp_like

Here's another way... rather than searching for a string of exactly one group of letters followed by numbers  you could have searched for the SECOND instance of a letter/number group.  For a legal string you wouldn't find one, for the illegal strings you were looking for you would find them.

The "trick" really, is in defining the pattern well.  Not the regexp, but the actual in-words description of what you are looking for.

Once you have that it's usually MUCH easier to write the regexp as a simple left-to-right search.

Your pattern is:

The string starts with (^) one or more  letters ( [MTRF] ) then those are followed by one or more numbers ( [0-9] ) and then the string ends ($).

Put that all together and you get...

^[MTWRFS]+[0-9]+$

If you defined your pattern differently, you'd get a different regexp.

The SQL appendix won't show you how to use them, but it includes the syntax pieces you need to know, and also, if you're reading other books, the absence of a feature (like forward references) in the appendix indicates Oracle does NOT support it (yet)
0
Gadsden ConsultingIT SpecialistAuthor Commented:
thanks a lot. I will review this in more detail tomorrow, and sleep on it tonight . . .
0
Gadsden ConsultingIT SpecialistAuthor Commented:
sdstuber,

thanks again, very helpful.

>>Here's another way... rather than searching for a string of exactly one group of letters followed by numbers  you could have searched for the SECOND instance of a letter/number group. For a legal string you wouldn't find one, for the illegal strings you were looking for you would find them.
-- good idea, but I couldn't get that to work, see below.

>>The "trick" really, is in defining the pattern well.  Not the regexp, but the actual in-words description of what you are looking for.
-- good point

-- so it works, but a couple of questions
    1. The pattern I'm looking for is "The string starts with (^) one or more  letters ( [MTRF] ) then those are followed by one or more numbers ( [0-9] ) and then the string ends ($).", so this is defined by ^[MWTRFS]+[0-9]+$
            - What if I wanted to make this legal: M5T34R56 (numbers follow letters), but MWF is illegal ?

   2. Why does this (Regexp_instr(a,'F5') return 2, where a = 'F5' ?

   3. Is a string denoted by a comma ? So to look for a comma, you backslash it ?

SELECT DISTINCT a, Regexp_instr(a,'(^[MWTRFS]+[0-9]+$)') AS "Pattern_Exists_Once",
            Regexp_instr(a,'F5') AS F5_found
FROM tab1
WHERE INSTR(a,',') = 0
   AND a IN ('M5T34R56','WF567','MWF','F123')

               Legal   F5_Found
WF567	         1	2
MWF	         0	0
F123	         1	0
M5T34R56	 0	0
 

Open in new window

 
And say I wanted to make MWF5T34R56 legal ? The pattern is "any letter (or multiples of) which is M,T,W,R,F,S followed by any number of 1,2,3,4,5,6,7,8,9,10" and this pattern can repeat ?

So only MWF above is illegal since it's not followed by numbers, but MWF1 is good.
0
sdstuberCommented:
1 - see below

2 - It doesn't,  I get 1 when I run this...

SELECT REGEXP_INSTR(a, 'F5') FROM (SELECT 'F5' a FROM DUAL)

3 - no, commas aren't significant characters except in counters like {3,7} - meaning, the previous pattern will be found 3 to 7 times sequentially;  but commas in the middle of a string don't matter.

However, your sample data doesn't have any commas

 AND a IN ('M5T34R56','WF567','MWF','F123')  - that condition alone means your data won't have any commas, the INSTR isn't really needed since it's redundant with the IN clause.

4 - "make MWF5T34R56 legal"   - since you want a repeating pattern, wrap the entire repeatable part in parentheses and then use + to indicate it will be found 1 or more times.

'([MTWRFS]+[0-9]+)+'


for example...


WITH tab1
     AS (SELECT 'M5T34R56' a FROM DUAL
         UNION ALL
         SELECT 'WF567' FROM DUAL
         UNION ALL
         SELECT 'MWF' FROM DUAL
         UNION ALL
         SELECT 'F123' FROM DUAL)
SELECT a,
       REGEXP_INSTR(a, '(^[MWTRFS]+[0-9]+$)') AS "Pattern_Exists_Once",
       REGEXP_INSTR(a, 'F5') AS f5_found,
       REGEXP_INSTR(a, '([MTWRFS]+[0-9]+)+') AS is_legal
  FROM tab1
0
Gadsden ConsultingIT SpecialistAuthor Commented:
sdstuber,

thanks again.

>>AND a IN ('M5T34R56','WF567','MWF','F123')  - that condition alone means your data won't have any commas, the INSTR isn't really needed since it's redundant with the IN clause.
- right, I was first limiting myself to no commas with INSTR(seti.mtg_time,',') = 0 then just further limited to the IN clause

>>I get 1 when I run this... SELECT REGEXP_INSTR(a, 'F5') FROM (SELECT 'F5' a FROM DUAL)
-- but this returns 2: SELECT REGEXP_INSTR(a, 'F5') FROM (SELECT 'WF567' a FROM DUAL)

>>since you want a repeating pattern, wrap the entire repeatable part in parentheses and then use + to indicate it will be found 1 or more times.
-- great ! that's really helpful
-- but this also returned legal (1)
REGEXP_INSTR(a, '([MWTRFS]+[0-9]+)') AS "Pattern_Exists_Multiple"
whereas you have an extra "+"
REGEXP_INSTR(a, '([MTWRFS]+[0-9]+)+') AS is_legal
0
sdstuberCommented:
>>>  but this returns 2: SELECT REGEXP_INSTR(a, 'F5') FROM (SELECT 'WF567' a FROM DUAL)

of course,  F5 is found at the 2nd character of WF567

INSTR isn't a boolean function of 0/1 it returns the position of the string if it finds it, 0 if not

"Pattern_Exists_Multiple" - that's a bad name,  the INSTR looks for the first time that expression is found, which is at character 1, the fact that there are repeats is meaningless

REGEXP_INSTR(a, '([MTWRFS]+[0-9]+)+')  -- this checks explicitly for repeats.

to be even more explicit, add the ^$ to indicate the pattern must be the entire string.

if not

then  '############MTRWF123@@@@@@@@@@@@@@'  would be a legal string because a legal pattern can be found inside that string


your original question has been answered, please close this one and open new questions rather than adding on here.
0
Gadsden ConsultingIT SpecialistAuthor Commented:
>>of course,  F5 is found at the 2nd character of WF567
- oops, I was focusing on the 1 for the entire pattern and had a brain freeze on that . . .

>>your original question has been answered, please close this one and open new questions rather than adding on here.
-- sure
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.