Solved

Create SQL Query to bring results from a field which does not follow a required format

Posted on 2009-03-29
14
483 Views
Last Modified: 2013-12-07
hmmm....In a particular table, namely 'Invoice', I have a field 'Due Date', by mistake there was no condition set for entering the data in the field from the application side because of which there are now wrong faulty data in that particular field. Now I need to inform users to rectify their mistake but for that I need to select the faulty set of data.

So I need a select query which should be able to show data from the field 'Due Date' if it does not follow the format, "(0?[1-9]|1[012])/(0?[1-9]|[12][0-9]|3[01])/(19|20)\d\d"

I am not a database guy, hence not sure if I am making sense, please tell me if more information is needed.
0
Comment
Question by:Madhusudanbanik
  • 8
  • 4
  • 2
14 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 150 total points
ID: 24016202
If this is oracle 10g or higher then you've pretty much already got it


select * from invoice where not regexp_like(due_date,'(0?[1-9]|1[012])/(0?[1-9]|[12][0-9]|3[01])/(19|20)\d\d')

if it's 9i or lower then regular expressions aren't supported
you'll have to write your own function then

try somthing like this...

select * from invoice where check_date_str(due_date) is null
CREATE OR REPLACE FUNCTION check_date_str(p_str IN VARCHAR2)

    RETURN DATE

IS

    v_temp   DATE;

BEGIN

    v_temp   := TO_DATE(p_str, 'mm/dd/yyyy');
 

    IF v_temp >= TO_DATE('1900-01-01', 'yyyy-mm-dd')

   AND v_temp <= TO_DATE('2099-12-31', 'yyyy-mm-dd')

    THEN

        -- valid date string and within 20th/21st centuries

        RETURN v_temp;

    ELSE

        -- valid date string but not wihtin legal range

        RETURN NULL;

    END IF;

EXCEPTION

    WHEN OTHERS

    THEN

        -- not a valid date string

        RETURN NULL;

END;

Open in new window

0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 100 total points
ID: 24016216
In 11g, you have a built in function named ISDATE to check whether a given value is a valid date or not.

In the earlier versions, we can achieve a similar thing using user defined functions.
A simple function like this will do:

Now run this query to find out which are all invalid records:

select * from urtable
where isdate(datecol) = 1
create or replace function isdate(p_inDate date, p_format varchar2) return number

as

 v_dummy date;

begin

 select to_date(p_inDate,p_format) into v_dummy from dual;

 return 0;

exception

 when others then return 1;

end isdate;

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24016220
Small Update:

Now run this query to find out which are all invalid records:

select * from urtable
where isdate(datecol, dateformat) = 1
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24016229
note the format above also restricts the date range to be within 20th or 21st century

merely checking for valid dates alone is not enough, they have to be valid and within the proper range
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24016248
rrjegan17,
I don't know about the isdate function in Oracle,  I don't see it in the 11g SQL Reference and it's not recognized in my 11.1.0.7 db.

Is it documented and supported?  If so, where?  I'd like to read more about the api for it.  
Does it come with a certain patch, if so, which one?  It would be a nice addition to the sql language.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24016272
sdstuber,
   I am using Oracle 10g and it don't have it.
When I searched for this asker, I found out that this is available in 11g.

Please find the reference below for your information

http://download.oracle.com/docs/cd/B28359_01/olap.111/b28126/dml_functions_1106.htm

Hope I clarified you out.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24018379
ok, you have to have the separately licensed OLAP stuff.  Which I do not in my db.

Thanks!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 24019632
it doesn't appear that isdate has the flexibility the asker is looking for
that is, you can't use it to specify a particular format and determine if the string follows that format
and, if you don't have OLAP you can't use it anyway.

but, thanks for the info anyway, the OLAP stuff is interesting even if not applicable to this question specifically
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24019679
one additional note

using regular expressions like above does allow you to check the format
but, even though it will enforce the centuries and look "mostly" like a date
it's still completely reliable.

'02/31/2009'  will pass the regular expression check but the string does not represent a valid date.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24019832
sdstuber,
   But the equivalent one given in my comment no 24016216 handles it out right.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24019879
yeah, same as my check_date_str except I include the century check
same idea and easy to include in yours too

That last note about regulare expressions wasn't directed to you, it was to the asker.  I wanted to make sure that even though I showed how to use the expression posted in the original question, it still wasn't a reliable way of finding good date strings
0
 
LVL 2

Author Comment

by:Madhusudanbanik
ID: 24020438
Though I am gonna accept both the solutions, since both of them gave the same result with the use of the additional function, but for some reason, none of them were able to show only the results which do not follow the format, it showed me all the results where duedate had any date. Luckily checking the results I found out that results not in proper format are only in tens and hence was able to rectify them manually.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24021068
" none of them were able to show only the results which do not follow the format, "

how were you using the function?

please provide an example that did not work
0
 
LVL 2

Author Comment

by:Madhusudanbanik
ID: 24025618
Hi sdstuber,

I am using toad, and ran the function through schema browser, then ran the query in normal editor. Even this query worked, since it was 10g but again with same result, i.e gave all the results which had any date in duedate column.

"select * from invoice where not regexp_like(due_date,'(0?[1-9]|1[012])/(0?[1-9]|[12][0-9]|3[01])/(19|20)\d\d')"

My assumption is now that, the format is basically for the application side where it checks for the input from user whenever the field is a date, for some reason, for this particular field 'duedate', this format criteria was not put in on the application side and Oracle/sql cannot differentiate based on this format?

The faulty dates were like this '07/31/0207' or '11/21/0007'.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Next Available Number 2 31
Help writing a query 6 71
Oracle SQL syntax check  without executing 6 34
How to free up undo space? 3 27
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now