Solved

# need help with REGEXP_SUBSTR or REGEXP_INSTR

Posted on 2014-10-27
261 Views
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.
0
• 8
• 7

LVL 73

Accepted Solution

sdstuber earned 500 total points
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

Author Comment

ok, thanks, I'll give it a try . . .
0

LVL 73

Expert Comment

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

LVL 73

Expert Comment

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

Author Comment

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

great !
0

Author Comment

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

LVL 73

Expert Comment

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

Author Comment

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

LVL 73

Assisted Solution

sdstuber earned 500 total points
>> - 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.

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

Author Comment

thanks a lot. I will review this in more detail tomorrow, and sleep on it tonight . . .
0

Author Comment

sdstuber,

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

``````

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

LVL 73

Assisted Solution

sdstuber earned 500 total points
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

Author Comment

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

LVL 73

Expert Comment

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

0

Author Comment

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

-- sure
0

## Featured Post

If you find yourself in this situation â€śI have used SELECT DISTINCT but Iâ€™m getting duplicatesâ€ť then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you aâ€¦
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alonâ€¦
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
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.