REGEXP_LIKE between number range

Question

Can someone please finalize the code on the below.

I only want to look for a 6 digit number range anywhere in the RMK field, between 100000 and 999999

 REGEXP_LIKE(RMKADH.RMK, '[[:digit:]]')

The current code works but is bringing back anything with a number so I'm trying to narrow it down to 6 digits together. I've tried a few but no luck.

Edit: I want to flag this field if a 6 digit number is present. The reference will always be 6 digits long only, no more no less. But as it's a free text field it could be anywhere and contain anything. Example output I do want to flag: >abc123456markj< = flagged. Output I don't want to flag: >Mark 23647282< because the number it finds is more than 6 characters in length I know it's not a valid reference.

Do you want the exact matches? What if 7100000 present in the field? `LIKE` would mean that it has 100000 and 710000 present in it, both of which are in the given range, but 7100000 itself is not. — Gurwinder Singh, Jan 16 '17 at 11:44
Please [edit] your question and add sample input / expected output. E.g. (as mentioned by @GurV): Should 7100000 be a match? What about A100000? Or 'A 1000000' ? Or '1000000.' ? — Frank Schmitt, Jan 16 '17 at 12:29

Serge · Accepted Answer · 2017-01-17T00:46:05.320

4

Try this:

REGEXP_LIKE(RMKADH.RMK, '[1-9][[:digit:]]{5}') AND length(RMKADH.RMK) = 6

For more info, see: Multilingual Regular Expression Syntax

edited Jan 17 '17 at 00:46

answered Jan 16 '17 at 11:42

Serge

3,986
2
17
37

It'll match 000000 – Gurwinder Singh Jan 16 '17 at 11:50
@Mark It **did not**. It'll not satisfy your criteria of number being in a given range. If the field had `ABC000000`, it'll show it in the result, when it should not. – Gurwinder Singh Jan 16 '17 at 11:53
@GurV is technically correct. If you may incur values longer than 6 characters long, then also include the `length` function. – Serge Jan 17 '17 at 00:48
Thanks Serge, can you explain what is the purpose of {5} ? – Mark Johnson Jan 19 '17 at 09:31
1

`[1-9][[:digit:]]{5}` means 1st digit between 1 and 9, followed by any digit (0-9) x 5 times. See [metacharacters](https://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm#1006826) documentation. – Serge Jan 19 '17 at 10:10

score 1 · Answer 2 · answered May 01 '20 at 08:29

If you want to get all the Records which have only Numeric values in them you can use below query

    REGEXP_LIKE(RMKADH.RMK, '^[[:digit:]]+$');

The above will match any number of Numbers from start to end in the string. So if your Numbers span from 1 digit to any number of Digits, this will be useful.

Gurwinder Singh · Answer 3 · 2017-01-16T12:00:00.613

0

You can do a REGEXP_SUBSTR to get 6 digits out of the given field and compare it using between

select * from t
where to_number(regexp_substr(col,'[[:digit:]]{6}')) between 100000 and 999999;
;

Please note that if a bigger sequence than 6 digits exists, the above solution will take first 6 digits into consideration. If you want to do for any 6 consecutive digits, the solution will have to be a different one.

edited Jan 16 '17 at 12:00

answered Jan 16 '17 at 11:51

Gurwinder Singh

38,557
6
51
76

score 0 · Answer 4 · edited Apr 13 '22 at 16:20

0

SELECT
    to_number(regexp_replace('abc123456markj', '[^[:digit:]]', '')) digits
FROM
    dual
WHERE
    REGEXP_LIKE('abc123456markj', '[[:digit:]]')
    AND
    length(regexp_replace('abc123456markj', '[^[:digit:]]', '')) = 6
    AND
    regexp_replace('abc123456markj', '[^[:digit:]]', '') BETWEEN 100000 AND 999999;

edited Apr 13 '22 at 16:20

Felix Geenen

2,465
1
28
37

answered Apr 13 '22 at 14:36

user3203483

1
3

1

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – Swier Apr 13 '22 at 16:38

REGEXP_LIKE between number range

4 Answers4