CODEWITHSUNDEEP
sqloracle-database
user1510914
user1510914

Reputation: 11

SQL Query for below

I have a requirement where one field in database is like 

select distinct workorder_ref,workorder_tag from repository.tblworkorder a
where  REGEXP_LIKE (workorder_tag,'^20130912');

it gives me output like,

workorder_ref   workorder_tag
AJYAMOI         20130912-050226
AJYAXDX         20130912-085750
AJYAXEC         20130912-085753
AJYBLGT         20130912-102759

But as you all can see in the query I have hardcoded the date yyyymmdd format (20130912) in example. So is there any way to go about it ? i was thinking something like below but it does not help.

select distinct workorder_ref from repository.tblworkorder a where REGEXP_LIKE
(workorder_tag,'((to_char(sysdate,'YYYYMMDD'))%)');

Upvotes: 0

Views: 101

Answers (3)

Sergej Panic
Sergej Panic

Reputation: 839

You've enclosed to_char() within quotes '....', making it a string: '((to_char(sysdate,'YYYYMMDD'))%)'. You need to place the quotes properly.

Also when using REGEXP_LIKE() you must use .* instead of %, because it performs regular expression matching instead of the simple % pattern matching performed by LIKE.

Corrected version:

select distinct workorder_ref from repository.tblworkorder a 
 where REGEXP_LIKE(workorder_tag, to_char(sysdate,'YYYYMMDD') || '.*');

But in your simple case you can just use LIKE:

select distinct workorder_ref from repository.tblworkorder a 
where workorder_tag LIKE to_char(sysdate,'YYYYMMDD')|| '%';

Upvotes: 1

Marco Baldelli
Marco Baldelli

Reputation: 3728

Try using this condition:

where workorder_tag like to_char(sysdate, 'yyyymmdd') || '%'

Upvotes: 3

Nadeem_MK
Nadeem_MK

Reputation: 7689

In SQL Server I think the function GETDATE() should solve your problem.

Or the function NOW() in MySQL

SYSDATE in Oracle

Upvotes: 0

Related Questions