CODEWITHSUNDEEP
sqlregexoracle-databasesqlplus
Sai Wai Maung
Sai Wai Maung

Reputation: 1617

Oracle db regexp to filter out rows that are not in a specific pattern

In an Oracle DB table, one of the column has the following VARCHAR2 format:

yyyy-mm-dd hh:mm:ss

I would love to filter out all rows that don't match this pattern. So, I wrote the following regexp in my query, but the query is returning all rows in the table including all rows that matches 2014-09-10 10:02:33 pattern.

SELECT COLUMN 
FROM TABLE
WHERE regexp_like(COLUMN, '^[[:digit:]{4},-,[:digit:]{2},-,[:digit:]{2}, ,[:digit:]{2},:,[:digit:]{2},:,[:digit:]{2}]');

What's the problem with my query?

Upvotes: 0

Views: 330

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can try this.

select col
from tablename
where regexp_like(col,'^([1-9]\d{3})-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])\s(0[0-9]|1[0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$')
  • [1-9]\d{3} - matches all years starting from 1000 to 9999
  • 0[1-9]|1[0-2] - matches months from 01 to 12
  • 0[1-9]|[1-2][0-9]|3[0-1] - matches days from 01 to 31
  • \s - matches space
  • 0[0-9]|1[0-9]|2[0-3] - matches hours from 00 to 23
  • [0-5][0-9] - matches minutes from 00 to 59, seconds from 00 to 59

The query would also give you 02-30-2016 etc. as valid dates. You need to include extra logic to match months with 30 days and February based on a leap year.

It is not recommended to store date as varchar. You can write a procedure that would return only valid dates when you convert it using to_date.

Edit: The below query would match all the valid dates except February 29 in a leap year.

select col
from tablename
where regexp_like(col,'^([1-9]\d{3})-((0[13578]|10|12)-([0-9]|[1-2][0-9]|3[0-1])|(0[469]|11)-(0[1-9]|[1-2][0-9]|30)|(02-(0[1-9]|[1-2][0-8])))\s(0[0-9]|1[0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$')

Upvotes: 2

Related Questions