'REGEXP_LIKE between number range

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.

sqlbetweenregexp-like


Solution 1:[1]

Try this:

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

For more info, see: Multilingual Regular Expression Syntax

Solution 2:[2]

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.

Solution 3:[3]

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.

Solution 4:[4]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Avinash Barnwal
Solution 4 Felix Geenen

Related Questions