Oracle, REGEXP_LIKE

Question

I have table with data:

43  SQL Developer   2016-04-25/14:15:18
43  SQL Developer   2016-04-25/14:11:41
43  SQL Developer   2016-04-26/11:11:11

and I want to show only rows where date is 25.04. I'm new in regexp, and this char strings like []][]/. always annoyed me.

What about a standard `like` clause: `where your_column like '%04-25%'`? Otherwise, see http://stackoverflow.com/a/22944075/1225328 for a quick introduction to regexes. — sp00m, Apr 26 '16 at 09:59
Is the date column really `varchar` or the `date` is displayed like what you gave. — Utsav, Apr 26 '16 at 10:02
Don't assume the datatype. Do a `describe tablename` and know for sure what the datatype is. — Gary_W, Apr 26 '16 at 14:39

score 1 · Answer 1 · answered Apr 26 '16 at 13:59

If you want to compare to a date (a value that is truly in datetime format), whether it is a value from a table in the database, a bind variable passed in at runtime, or any other kind of true "datetime" tipe, you can compare like this:

... where to_date(substr(your_col, 1, instr(your_col, '/')-1), 'yyyy-mm-dd') = date_param

Here your_col is the name of the column you illustrated in your question (holding those strings that "look like" date/time), and date_param is the date you want to compare to.

Note - if the date_param may have a TIME component (other than 00:00:00), you will need to have trunc(date_param) on the right-hand side. On the left-hand side you don't need trunc() since to_date used the way I showed already gives you a "truncated" data (with the time set to 00:00:00 by default).

Good luck!

Gary_W · Accepted Answer · 2016-04-27T20:37:19.423

My first inclination was to do date operations, but since the question was asked in terms of REGEXP_LIKE I'll assume the OP wants to use string operations:

Converting column 3 to a date, then extracting where the month = 4 and the day = 25:

with tbl(col1, col2, col3) as (
  select 43, 'SQL Developer', to_date('2016-04-25/14:15:18', 'YYYY-MM-DD/HH24:MI:SS') from dual union
  select 43, 'SQL Developer', to_date('2016-04-25/14:11:41', 'YYYY-MM-DD/HH24:MI:SS') from dual union
  select 43, 'SQL Developer', to_date('2016-04-26/11:11:11', 'YYYY-MM-DD/HH24:MI:SS') from dual
)
select col1, 
       col2,
       col3
from tbl
where EXTRACT(month FROM col3 ) = 4
and EXTRACT(day FROM col3 ) = 25;

Using REGEXP_LIKE:

with tbl(col1, col2, col3) as (
  select 43, 'SQL Developer', '2016-04-25/14:15:18' from dual union
  select 43, 'SQL Developer', '2016-04-25/14:11:41' from dual union
  select 43, 'SQL Developer', '2016-04-26/11:11:11' from dual
)
select col1, 
       col2,
       col3
from tbl
where regexp_like(col3, '04-25\/');

This assumes a lot about the date/time column which is stored in a varchar2 (arguably a bad idea). It's always in this format, it's not NULL, the separator is always a slash, if input comes from a screen form its validated and scrubbed to make sure it matches this format, etc. You may want to do a search on the date/time column for unexpected formats to make sure you really know what you are dealing with. Since it's a varchar2 you really can't be sure (one of the problems with a date being stored in that datatype).

I HIGHLY recommend you store this date/time in a proper date column and save yourself some trouble if you have that authority.

EDIT: You can tighten it up too by using this REGEXP_SUBSTR to compare. The regex makes it match the entire line, specifically matching the captured group of month/day to what you want. If the pattern is not found, NULL is returned so handle that by surrounding it with a NVL that reurns 'VALUE NOT FOUND' or whatever is appropriate maybe:

where regexp_substr(col3, '^\d{4}-(\d{2}-\d{2})\/\d{2}:\d{2}:\d{2}$', 1,1,null,1) = '04-25';

SYSDATE supports this format (2016-04-25/14:15:18)? Mayby SYSTIMESTAMP? — Michał M, Apr 28 '16 at 08:05

Oracle, REGEXP_LIKE

2 Answers2