1

One of my table column should have values as Concatenation of a percentage from 0 to 100 and one of the following values (R, MR, MS or S). Eg: 20MS.

Should I use REGEXP_COUNT or SIMILAR TO, to check data validity. Tried this but does not work:

SELECT REGEXP_COUNT('6A', '^[0-9]+(R|MR|MS|S)?')

Any suggestions or solutions?

buddemat
  • 4,552
  • 14
  • 29
  • 49
Satish Takkalapalli
  • 23
  • 2

1 Answers1

1

Use

where column SIMILAR TO '[0-9]+(R|MR|MS|S)'

If the value after percentage is optional, use

where column SIMILAR TO '[0-9]+(R|MR|MS|S)?'

Using a regex operator ~ you can achieve

where column ~ '^[0-9]+(M?[RS])?$'

See regex proof.

Explanation

--------------------------------------------------------------------------------
  ^                        the beginning of the string
--------------------------------------------------------------------------------
  [0-9]+                   any character of: '0' to '9' (1 or more
                           times (matching the most amount possible))
--------------------------------------------------------------------------------
  (                        group and capture to \1 (optional
                           (matching the most amount possible)):
--------------------------------------------------------------------------------
    M?                       'M' (optional (matching the most amount
                             possible))
--------------------------------------------------------------------------------
    [RS]                     any character of: 'R', 'S'
--------------------------------------------------------------------------------
  )?                       end of \1 (NOTE: because you are using a
                           quantifier on this capture, only the LAST
                           repetition of the captured pattern will be
                           stored in \1)
--------------------------------------------------------------------------------
  $                        before an optional \n, and the end of the
                           string
Ryszard Czech
  • 18,032
  • 4
  • 24
  • 37
  • Thank you for the solution. This accepts any number, in the number part. How can we validate the numeric part to be between 0 to 100. – Satish Takkalapalli Dec 01 '20 at 03:56
  • @SatishTakkalapalli You can [take this one](https://stackoverflow.com/questions/13473523/regex-number-between-1-and-100). `'^([1-9][0-9]?|100)(M?[RS])?$'` – Ryszard Czech Dec 01 '20 at 20:24