add column check for format number to number oracle

Question

I need to add a column to a table that check for input to be a max value of 999 to 999, like a soccer match score. How do I write this statement?

example:

 | Score |
 ---------
 | 1-2   |
 | 10-1  |
 |999-999|
 | 99-99 |

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE SCORES (Score ) AS
          SELECT '1-2' FROM DUAL
UNION ALL SELECT '10-1' FROM DUAL
UNION ALL SELECT '999-999' FROM DUAL
UNION ALL SELECT '99-99' FROM DUAL
UNION ALL SELECT '1000-1000' FROM DUAL;

Query 1:

SELECT SCORE,
       CASE WHEN REGEXP_LIKE( SCORE, '^\d{1,3}-\d{1,3}$' )
            THEN 'Valid'
            ELSE 'Invalid'
            END AS Validity
FROM   SCORES

Results:

|     SCORE | VALIDITY |
|-----------|----------|
|       1-2 |    Valid |
|      10-1 |    Valid |
|   999-999 |    Valid |
|     99-99 |    Valid |
| 1000-1000 |  Invalid |