Using regexp_like in Oracle to match on multiple string conditions using a range of values

Question

I have a field in my Oracle DB that contains codes and from this I need to pull multiple values using a range of values.

As an example I need to pull all codes in the range C00.0 - C39.9 i.e. begins with C, the second character can be 0-3, third character is 0-9, followed by a "." and then the last digit is 0-9 e.g.

CODES
-----
C00.0
C10.4
C15.8
C39.8

The example above is for one pattern, I have multiple patterns to match on, here is another example

C50.011-C69.92

Again, starts with C, second character is 5-6, third is 0-9, fourth is ".", fifth is 0-9, sixth is 1-2 etc.

I have tried the following but my pipe function doesn't appear to pick up the second condition and therefore I am only getting results for the first condition '^[C][0-3][0-9][.][0-9]':

SELECT DISTINCT CODES
FROM
TABLE
WHERE REGEXP_LIKE (CODES,   '^[C][0-3][0-9][.][0-9]|
                             ^[C][4][0-3][.][0-9]|
                             ^[C][4][A][.][0-9]|
                             ^[C][4][4-9][.][0-9]|
                             ^[C][4][9][.][A][0-9]|
                             ^[C][5-6][0-9][.][0-9][1-9]|
                             ^[C][7][0-5][.][0-9]|
                             ^[C][7][A-B][.][0-8]')
ORDER BY CODES

I would be very grateful if anyone could make a suggestion on how I can pull the additional patterns.

Answer 1

You have newlines in the pattern -- in other words, your attempt at readability is causing the problem. You can just remove them, although I would probably factor out common elements:

WHERE REGEXP_LIKE (CODES, '^[C]([0-3][0-9][.][0-9]|[4][0-3][.][0-9]|[4][A][.][0-9]|[4][4-9][.][0-9]|[4][9][.][A][0-9]|[5-6][0-9][.][0-9][1-9]|[7][0-5][.][0-9]|[7][A-B][.][0-8])')

I think you also want $ at the end.

If you want readability, you could use or:

SELECT DISTINCT CODES
FROM TABLE
WHERE REGEXP_LIKE (CODES, '^[C][0-3][0-9][.][0-9]') OR
      REGEXP_LIKE (CODES, '^[C][4][0-3][.][0-9]|') OR
      . . . 

Answer 2

Here is a regex pattern for what you want to match here:

^C[0-3][0-9][.][0-9]$

Demo

This would match the range of C00.0 - C39.9. If you want to match other ranges, then you would need an alternation with another pattern to cover those ranges.

Applying this to your current query:

SELECT DISTINCT CODES
FROM yourTable
WHERE REGEXP_LIKE (CODES, '^C[0-3][0-9][.][0-9]$');