How to find a specific mask within a string - Orac

Question

I have a field in a table that can be informed with differente values. Examples:

Row 1 - (2012,2013) 
Row 2 - 8871 
Row 3 - 01/04/2012 
Row 4 - 'NULL' 

I have to identify the rows that have a string with a date mask 'dd/mm/yyyy' informed. Like Row 3, so I may add a TO_DATE function to it.

Any idea on how can I search a mask within the field?

Thanks a lot

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Test ( id, VALUE ) AS
          SELECT 'Row 1', '(2012,2013)' FROM DUAL
UNION ALL SELECT 'Row 2', '8871' FROM DUAL
UNION ALL SELECT 'Row 3', '01/04/2012' FROM DUAL
UNION ALL SELECT 'Row 4', NULL FROM DUAL
UNION ALL SELECT 'Row 5', '99,99,2015' FROM DUAL
UNION ALL SELECT 'Row 6', '32/12/2015' FROM DUAL
UNION ALL SELECT 'Row 7', '29/02/2015' FROM DUAL
UNION ALL SELECT 'Row 8', '29/02/2016' FROM DUAL
/

Query 1 - You can check with a regular expression:

SELECT *
FROM   TEST
WHERE  REGEXP_LIKE( VALUE, '^\d{2}/\d{2}/\d{4}$' )

Results:

|    ID |      VALUE |
|-------|------------|
| Row 3 | 01/04/2012 |
| Row 6 | 32/12/2015 |
| Row 7 | 29/02/2015 |
| Row 8 | 29/02/2016 |

Query 2 - You can make the regular expression more complicated to catch more invalid dates:

SELECT *
FROM   TEST
WHERE  REGEXP_LIKE( VALUE, '^(0[1-9]|[12]\d|3[01])/(0[1-9]|1[0-2])/\d{4}$' )

Results:

|    ID |      VALUE |
|-------|------------|
| Row 3 | 01/04/2012 |
| Row 7 | 29/02/2015 |
| Row 8 | 29/02/2016 |

Query 3 - But the best way is to try and convert the value to a date and see if there is an exception:

CREATE OR REPLACE FUNCTION is_Valid_Date(
  datestr VARCHAR2,
  format  VARCHAR2 DEFAULT 'DD/MM/YYYY'
) RETURN NUMBER DETERMINISTIC
AS
  x DATE;
BEGIN
  IF datestr IS NULL THEN
    RETURN 0;
  END IF;
  x := TO_DATE( datestr, format );
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;
/


SELECT *
FROM   TEST
WHERE  is_Valid_Date( VALUE ) = 1

Results:

|    ID |      VALUE |
|-------|------------|
| Row 3 | 01/04/2012 |
| Row 8 | 29/02/2016 |

Answer 2

Sounds like a data model problem (storing a date in a string).

But, since it happens and we sometimes can't control or change things, I usually keep a function around like this one:

CREATE OR REPLACE FUNCTION safe_to_date (p_string        IN VARCHAR2,
                                         p_format_mask   IN VARCHAR2,
                                         p_error_date    IN DATE DEFAULT NULL)
  RETURN DATE
  DETERMINISTIC IS
  x_date   DATE;
BEGIN
  BEGIN
    x_date   := TO_DATE (p_string, p_format_mask);
    RETURN x_date;                                                        -- Only gets here if conversion was successful
  EXCEPTION
    WHEN OTHERS THEN
      RETURN p_error_date;
  END;
END safe_to_date;

Then use it like this:

WITH d AS
       (SELECT 'X' string_field FROM DUAL
        UNION ALL
        SELECT '11/15/2012' FROM DUAL
        UNION ALL
        SELECT '155' FROM DUAL)
SELECT safe_to_date (d.string_field, 'MM/DD/YYYY')
FROM   d;

Answer 3

You can use the like operator to match the pattern.

where possible_date_field like '__/__/____';