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

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.

Answer

Try this:

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

For more info, see: Multilingual Regular Expression Syntax

Advertisement

Answer