Home » SQL & PL/SQL » SQL & PL/SQL » Extract date from string (Oracle 10g)
Extract date from string [message #574255] Tue, 08 January 2013 04:39 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I need to extract the First portion of date entered in remarks column , i have prepared a test as below.



CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));

INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');

INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');

INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');

INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');

INSERT INTO LEAVE_DYS VALUES ('R0313','LEAVE SALARY FOR SEPT. 9, 2010 - SEPT. 8, 2012 (35 ABSENTS)');

COMMIT;



SELECT * FROM LEAVE_DYS;



LV_EMP_CODE	LV_DESC
R0754	LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)
R0667	LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)
R0841	LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)
R0854	LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)
R0313	LEAVE SALARY FOR SEPT. 9, 2010 - SEPT. 8, 2012 (35 ABSENTS)

--Required output is

LV_EMP_CODE  LV_DESC
R0754        16/04/2012
R0667        14/07/2012
R0841        29/05/2012
R0854        29/05/2012
R0313        09/09/2012





[Updated on: Tue, 08 January 2013 04:41]

Report message to a moderator

Re: Extract date from string [message #574259 is a reply to message #574255] Tue, 08 January 2013 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The dates are between 'FOR ' and ' - ', use INSTR and SUBSTR to extract the date string.
To convert string to date, use TO_DATE.
You should be able to do it by yourself now. Anyone with 4 years in SQL should be able, especially with all the helps we gave you.

Regards
Michel
Re: Extract date from string [message #574292 is a reply to message #574259] Tue, 08 January 2013 11:59 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks michel for the advice and help you gave me earlier, i managed to write the query but there is one problem while converting it to a date format.


SELECT ltrim(rtrim(replace(TO_CHAR((SUBSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1),
               INSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1), 'FOR')
               + 3
              ))),'.',''))) A 
  FROM leave_dys  


A
APRIL 16, 2010
JULY 14, 2009
MAY 29, 2010
MAY 29, 2010
SEPT 9, 2010

--when i am converting it to date by using to_date its giving an error , ORA-01858 a non numeric character was found when numeric was expected.

SELECT to_date(ltrim(rtrim(replace(TO_CHAR((SUBSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1),
               INSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1), 'FOR')
               + 3
              ))),'.',''))) ) A 
  FROM leave_dys  





[Updated on: Tue, 08 January 2013 12:10]

Report message to a moderator

Re: Extract date from string [message #574293 is a reply to message #574292] Tue, 08 January 2013 12:17 Go to previous messageGo to next message
BlackSwan
Messages: 22542
Registered: January 2009
Senior Member
classic example of why is it such a Bad Thing (tm) to use VARCHAR2 to store date related values!
Re: Extract date from string [message #574294 is a reply to message #574292] Tue, 08 January 2013 12:17 Go to previous messageGo to next message
Littlefoot
Messages: 19335
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
TO_DATE expects format mask as it was unable to match current NLS settings with data returned by your query.

In your example, "SEPT" looks suspicious (there's no such month, it is either "SEP" or "SEPTEMBER").
Re: Extract date from string [message #574296 is a reply to message #574294] Tue, 08 January 2013 12:38 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
@ arif_md2009

In addition, for future data modelling, you may want to take a look at Normalization


Regards,
Dariyoosh
Re: Extract date from string [message #574297 is a reply to message #574292] Tue, 08 January 2013 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You succeed the first step.
Now have a look at documentation; TO_DATE and Datetime Format Models.

Regards
Michel
Re: Extract date from string [message #574306 is a reply to message #574293] Tue, 08 January 2013 21:44 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks everybody for your valuable comments and assistance,i dont think it has to do something with normalization as this is just an additional information where user has entered the date in remarks and i am just helping him to extract the date part.
Re: Extract date from string [message #574307 is a reply to message #574294] Tue, 08 January 2013 21:52 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Hi littlefoot , i even tried changing it to SEP and SEPTEMBER from SEPT. but still the same error.
Re: Extract date from string [message #574309 is a reply to message #574297] Tue, 08 January 2013 22:09 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks Michel ,Littlefoot,Blackswan and dariyoosh for the great help , finally i did it by adding the nls_date_language in my query which goes like below.


SELECT TO_DATE(ltrim(rtrim(replace(((SUBSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1),
               INSTR (SUBSTR (lv_desc, 1, INSTR (lv_desc, '-') - 1), 'FOR')
               + 3
              ))),'.',''))),'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')  A 
  FROM leave_dys

A
04/16/2010 00:00:00
07/14/2009 00:00:00
05/29/2010 00:00:00
05/29/2010 00:00:00
09/09/2010 00:00:00



Re: Extract date from string [message #574326 is a reply to message #574309] Wed, 09 January 2013 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thank you for the feedback and solution.

Regards
Michel
Re: Extract date from string [message #574348 is a reply to message #574309] Wed, 09 January 2013 06:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1966
Registered: January 2010
Senior Member
Or, using regular expressions:

SELECT TO_DATE(
               REGEXP_REPLACE(
                              lv_desc,
                              '^LEAVE SALARY FOR (...)[^ ]* (\d\d?, \d{4}).*$',
                              '\1 \2'
                             ),
               'MON DD, YYYY',
               'NLS_DATE_LANGUAGE = American'
              ) A 
  FROM  leave_dys
/

A
---------
16-APR-10
14-JUL-09
29-MAY-10
29-MAY-10
09-SEP-10

SQL>


SY.
Re: Extract date from string [message #574350 is a reply to message #574348] Wed, 09 January 2013 06:48 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks solomon, for a very good solution, actually i am not used to regexp and i need to know or read more on this , where can i get good information on this with some examples and can you explain how this works.
Re: Extract date from string [message #574353 is a reply to message #574350] Wed, 09 January 2013 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Regular expressions are not Oracle specific.
You can use Google to find materials on them.
As usual I should say.

Regards
Michel
Re: Extract date from string [message #574389 is a reply to message #574353] Wed, 09 January 2013 21:42 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks michel , i found one interesting website on regular expressions http://www.regular-expressions.info/.Appreciate your prompt response.
Re: Extract date from string [message #574398 is a reply to message #574389] Wed, 09 January 2013 23:22 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
if you want 2012 as a year in your output, then just extract Day and Month from the string, attach the required Year to it and then convert it to date.

regards,
Delna
Previous Topic: How to Export Package Body using Spool Command
Next Topic: Delete records from child and parent table [merged 2 by jd]
Goto Forum:
  


Current Time: Thu Jul 31 00:33:38 CDT 2014

Total time taken to generate the page: 0.23874 seconds