Match any values ​​except a list of values ​​- reacx oracle

Question

I need an Oracle regular expression that matches a file name in the format ABCD_EFG_YYYYMMDD_HH(24)MISS.csv , except that the temporary part is one of three specific values: 110000 , 140000 or 180000 .

So, for example, it will match the file name ABC_DEF_20120925_110001.csv , but not the file name ABCD_EFG_20120925_110000.csv not.

The following non-Oracle regex works:

 ^ABCD_EFG_[0-9]*_(?!110000|140000|180000)[0-9]*\.csv$ 

but I don’t know how to write it as an Oracle regular expression.

Answer 1

Oracle does not support lookahead statements, so you will need to specify all valid matches:

 ^ABCD_EFG_[0-9]*_([02-9]|1[0235679]|1[148]0{0,3}[1-9])[0-9]*\.csv$ 

should work (provided that the time part is always 6 digits).

Explanation:

 ABCD_EFG_ # Match ABCD_EFG_ [0-9]*_ # Match first number (date part) and _ ( # Match a number that starts with [02-9] # 0 or 2-9 | # or 1[0235679] # 1, followed by 2,3,5,6,7, or 9 | # or 1[148] # 11, 14, or 18 0{0,3} # followed by up to three zeroes [1-9] # but then one digit 1-9 ) # End of alternation [0-9]* # Fill the rest with any digits \.csv # Match .csv (mind the backslash!)