Home » SQL & PL/SQL » SQL & PL/SQL » Format check on varchar check!! (Oracle 11g)
Format check on varchar check!! [message #637411] Sun, 17 May 2015 06:31 Go to next message
kaltiimaar
Messages: 6
Registered: May 2015
Junior Member
i m trying to check mm/dd/yyyy format on varchar datatype column??
i have tried below query... in below query a2 is varchar2 datatyoe column...
example:
select a2,case when (regexp_like(a2,'(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d') or
regexp_like(a2,'([1-9]|1[012])[- /.]([1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d') or a2 is null ) then 'valid' else 'invalid' end validd
from TEST_DATATYPE;

a2 validd
12/03/2006 valid
2006/13/02 invalid
2006/12/12 invalid
2006/12/12 invalid
13/13/2006 valid
13/13/20 invalid
3/3/2006 valid
03/3/2006 valid
3/32/2006 invalid
13/13/2152 invalid
15/3/2006 valid
15/3/2006 valid
23/13/2152 invalid

Here i want second last value to be invalid as well, because it is not valid date???
can anyone help on this??
Re: Format check on varchar check!! [message #637412 is a reply to message #637411] Sun, 17 May 2015 06:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
DATE doesn't have any format, what you see is for display so that it could be interpreted by humans. It is stored in an internal format by Oracle in 7 bytes.

It is a bad idea to do what you are trying to do. Store them as DATE and not VARCHAR.
Re: Format check on varchar check!! [message #637414 is a reply to message #637412] Sun, 17 May 2015 06:59 Go to previous messageGo to next message
kaltiimaar
Messages: 6
Registered: May 2015
Junior Member
But in case if we have column having varchar datatype and storing date in it??
later we are going to convert in date but before that i just wanted to filter data not having format as mm/dd/yyyy??
Re: Format check on varchar check!! [message #637415 is a reply to message #637414] Sun, 17 May 2015 07:12 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Tell me in words what is below date -

01/02/2015?

Is it 1st Feb 2015 or 2nd Jan 2015?
Re: Format check on varchar check!! [message #637416 is a reply to message #637415] Sun, 17 May 2015 07:21 Go to previous messageGo to next message
kaltiimaar
Messages: 6
Registered: May 2015
Junior Member
its 2nd jan 2015
Re: Format check on varchar check!! [message #637417 is a reply to message #637416] Sun, 17 May 2015 07:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
  1* select TO_DATE('01/02/2015','DD/MM/YYYY') FROM DUAL
SQL> /

TO_DATE('
---------
01-FEB-15

SQL> ed
Wrote file afiedt.buf

  1* select TO_DATE('01/02/2015','MM/DD/YYYY') FROM DUAL
SQL> /

TO_DATE('
---------
02-JAN-15

Re: Format check on varchar check!! [message #637418 is a reply to message #637417] Sun, 17 May 2015 08:07 Go to previous messageGo to next message
kaltiimaar
Messages: 6
Registered: May 2015
Junior Member
that's true..

But what i really wanted is to updated a flag value in table, so if i convert varchar2 using to_date(col,'mm/dd/yyyy') and if it is not in format specified the query will through error.

12/03/2006
2006/13/02
2006/12/12
2006/12/12
13/13/2006
13/13/20
3/3/2006
03/3/2006
3/32/2006
13/13/2152
15/3/2006
15/3/2006
23/13/2152

for values above i need only those values which is having mm/dd/yyyy format..
Re: Format check on varchar check!! [message #637419 is a reply to message #637416] Sun, 17 May 2015 08:08 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
kaltiimaar wrote on Sun, 17 May 2015 07:21
its 2nd jan 2015

Is it? How can you be certain?
Re: Format check on varchar check!! [message #637420 is a reply to message #637418] Sun, 17 May 2015 08:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: Format check on varchar check!! [message #637421 is a reply to message #637411] Sun, 17 May 2015 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

I disagree having a field which is not of the datatype of the data it contains.
You can use a function such:
create or replace function check_date (
  p_date   varchar2,
  p_format varchar2 default 'MM/DD/YYYY'
  )
return varchar2
deterministic
is
  l_date date;
begin
  l_date := to_date(p_date, p_format);
  return 'Valid';
exception
  when others then
    if sqlcode between -1866 and -1830 then
      return 'Invalid';
    else raise;
    end if;
end;
/

then
select a2, check_date(a2) from mytable;

Re: Format check on varchar check!! [message #637422 is a reply to message #637418] Sun, 17 May 2015 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
and if it is not in format specified the query will through error.


In this case just use TO_DATE (with the specific mask) on your field and you will if the field is a valid date or not.

Re: Format check on varchar check!! [message #637423 is a reply to message #637419] Sun, 17 May 2015 08:18 Go to previous messageGo to next message
kaltiimaar
Messages: 6
Registered: May 2015
Junior Member
because if we define a column to date datatype, the default format is mm/dd/yyyy..
But if column is of varchar2 datatype, it can store any value..
Re: Format check on varchar check!! [message #637424 is a reply to message #637423] Sun, 17 May 2015 08:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
kaltiimaar wrote on Sun, 17 May 2015 06:18
because if we define a column to date datatype, the default format is mm/dd/yyyy..
But if column is of varchar2 datatype, it can store any value..


If you do not allow invalid values to be stored, then no need to validate the content later.
Re: Format check on varchar check!! [message #637425 is a reply to message #637422] Sun, 17 May 2015 08:21 Go to previous messageGo to next message
kaltiimaar
Messages: 6
Registered: May 2015
Junior Member
but i wanted to update flag field.. if it not in specified format the query fails and does not update flag field...
i don't want query to fail if it is not in specified format..

like..

02/01/2015 Y
13/02/2015 N
02/13/2015 Y
2006/13/02 N

Re: Format check on varchar check!! [message #637426 is a reply to message #637425] Sun, 17 May 2015 08:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
UPDATE is not required if VIRTUAL COLUMN is used
http://docs.oracle.com/database/121/SQLRF/expressions005.htm#SQLRF20043
Re: Format check on varchar check!! [message #637428 is a reply to message #637423] Sun, 17 May 2015 09:55 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
because if we define a column to date datatype, the default format is mm/dd/yyyy..


There is no format to a DATE datatype.
YOU define the format you want to use it.
Examples:
SQL> select sysdate from dual;
SYSDATE
-------------------
17/05/2015 16:53:10

MY default format is not yours.
SQL> alter session set nls_date_format='MM/DD/YYYY';

Session altered.

SQL> select sysdate from dual;
SYSDATE
----------
05/17/2015

Now it is the same than yours.
But at any time SYSDATE as an internal format we don't care, and this is also true for internal format of a stored DATE data.

Previous Topic: Inserting records in Table using cursor
Next Topic: Calculation of Day, month and year
Goto Forum:
  


Current Time: Thu Mar 28 09:18:03 CDT 2024