Oracle select query with substr

Question

I have a table which looks like:

DAYDATE     TIMEOFDAY   USERID
18-JUL-12   Afternoon   mistu4u
18-JUL-12   Noon        mistu4u
20-JUL-12   Noon        mistu4u
21-JUL-12   Afternoon   mistu4u
11-SEP-12   Afternoon   rimo
06-AUG-12   Noon        rimo
14-JUN-12   Noon        mistu4u

Now I want to fetch those values for mistu4u whose daydate's month is 'jul'.I tried like

select * from mealdb where userid='mistu4u' and substr(daydate,4,3)='jul';

But it is giving 'no data found'.

Also anybody can suggest if the same can be acheived through "LIKE" keyword!! — Mistu4u, Jul 19 '12 at 13:38
What is the datatype of `DAYDATE`? You want July of any year? — Tebbe, Jul 19 '12 at 13:38
I just resolved it.My installation is case sensitive.That is what was creating problem.Thanks!! — Mistu4u, Jul 19 '12 at 13:43

score 3 · Accepted Answer · answered Jul 19 '12 at 13:39

3

Is your install case sensitive? Does this SQL work?

select * from mealdb where userid='mistu4u' and substr(daydate,4,3)='JUL';

answered Jul 19 '12 at 13:39

ProfessionalAmateur

4,447
9
46
63

OMG!!It is!!It just worked!!But sure I want to make it case insensitive.@ProfessionalAmateur can you tell me how to make it so?Also can you suggest how to write it with "LIKE". – Mistu4u Jul 19 '12 at 13:43
@Subir Adhikari - It can be done. Check [here](http://stackoverflow.com/questions/5391069/make-oracle-sql-like-query-case-insensitive) – ProfessionalAmateur Jul 19 '12 at 14:01

score 0 · Answer 2 · answered Jul 19 '12 at 13:41

0

Try

SELECT *
FROM   mealdb
WHERE  userid = 'mistu4u'
AND    TO_NUMBER(TO_CHAR(daydate,'MM')) = 7
;

answered Jul 19 '12 at 13:41

Tebbe

1,372
9
12

OraNob · Answer 3 · 2012-07-19T13:58:46.703

0

try

TRUNC(daydate, 'MONTH') = '01-JULY-2012'

Be careful if you have any indexes on the daydate column. The substr will render it useless. Perhaps, consider using a BETWEEN instead.

Also, most of the solutions will pick up all records in any year for the month of July.

edited Jul 19 '12 at 13:58

answered Jul 19 '12 at 13:41

OraNob

684
3
9

score 0 · Answer 4 · edited Nov 23 '16 at 15:57

0

Try with this:

select * from mealdb where userid='mistu4u' and daydate like '%JUL%';

edited Nov 23 '16 at 15:57

Zeb

1,715
22
34

answered Nov 23 '16 at 15:12

HElper

1

score 0 · Answer 5 · answered Oct 08 '20 at 04:49

0

select *
from mealdb
where userid='mistu4u'
and upper(daydate) LIKE '%JUL%';

answered Oct 08 '20 at 04:49

Imran

171
1
5

Oracle select query with substr

5 Answers5