Home » SQL & PL/SQL » SQL & PL/SQL » date in varchar column
date in varchar column [message #413250] Wed, 15 July 2009 00:36 Go to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I have an issue.Date field(dd-mm-yyyy) has been stored in a table with varchar column and there are some other rows in that column that contain other nondate values.
I know its idiotic to store date in varchar column.But that table is very huge some 10M rows.So I need to select only those values that are dates.So When I put any filter on date the performance degrades.
create table test
(
a varchar2(40)
);
insert into test values('22-01-2009');
insert into test values('30-09-1999');
insert into test values ('ayush');

So what I need here is I select all but display only when its a date.

[Updated on: Wed, 15 July 2009 00:37]

Report message to a moderator

Re: date in varchar column [message #413257 is a reply to message #413250] Wed, 15 July 2009 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "isdate".
But as you said you have to pay the silly implementation of date, anyway you do it.

Regards
Michel
Re: date in varchar column [message #413264 is a reply to message #413250] Wed, 15 July 2009 01:00 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
ayush_anand wrote on Wed, 15 July 2009 00:36

So When I put any filter on date the performance degrades.



Hope the below link would be helpful for you to understand...
http://tkyte.blogspot.com/2006/11/see-you-just-cannot-make-this-stuff-up.html


Regards,
prachi

[Updated on: Wed, 15 July 2009 01:02]

Report message to a moderator

Re: date in varchar column [message #413270 is a reply to message #413257] Wed, 15 July 2009 01:19 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Thanks Michel
I used the following code for solving my purpose and will really appreciate new Ideas.
select decode(regexp_instr(a,'^[ [:digit:]]{2}-[ [:digit:]]{2}-[ [:digit:]]{4}$' ),1,a,null) from test

in this case it will also take 99-99-9999 as a date but it serves my purpose

[Updated on: Wed, 15 July 2009 01:23]

Report message to a moderator

Previous Topic: Querying a materialized view while its refresh is on.
Next Topic: eliminating group by
Goto Forum:
  


Current Time: Tue Dec 06 12:31:24 CST 2016

Total time taken to generate the page: 0.09501 seconds