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.
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';
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!
likeclause:where your_column like '%04-25%'? Otherwise, see stackoverflow.com/a/22944075/1225328 for a quick introduction to regexes.varcharor thedateis displayed like what you gave.describe tablenameand know for sure what the datatype is.