OraFAQ Forum: SQL & PL/SQL » day,month year regex

Home » SQL & PL/SQL » SQL & PL/SQL » day,month year regex (oracle 10g, unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

day,month year regex [message #600295]

Mon, 04 November 2013 13:36

babhuko
Messages: 49
Registered: December 2011
Location: Chennai

Member

Hi all,

I tried to find the pattern below

Tuesday, October 08, 2013

by giving the regeexp_substr function, but the result am getting is "Tuesday". Please throw some light on this.

REGEXP_substr(timestamp,'^Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday, January|February|March|April|May|June
July|August|September|October|November|December \d{2}, \d{4}$',1,1)

And also what would be regex for

Tuesday, October 08, 2013 5:58:30 AM

Thanks,
babhuko

Report message to a moderator

Re: day,month year regex [message #600296 is a reply to message #600295]

Mon, 04 November 2013 13:44

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

If the purpose is to select rows matching to a pattern and not extract data according to a given pattern, then I think regexp_like is what you're looking for (instead of regexp_substr). Given the fact that you didn't provide a test case, it is not clear however what is exactly the requirement to implement.

[Updated on: Mon, 04 November 2013 13:47]

Report message to a moderator

Re: day,month year regex [message #600297 is a reply to message #600296]

Mon, 04 November 2013 13:51

babhuko
Messages: 49
Registered: December 2011
Location: Chennai

Member

Hi,

I have to extract the data according to the given pattern as well as the starting and ending positions of the given pattern. Lets say I have that pattern for five times, I need to know the the starting position of all the patterns and ending positions and also the data. I got stuck in the first itself so did not mention the full requirement. I need to know these things because I have to do processing between 1 and 2nd pattern and once its done, I have to do some processing between 2nd and 3rd pattern. Its a big process, but got stuck in finding the pattern, positions itself.

Thanks,
babhuko

[Updated on: Mon, 04 November 2013 13:58]

Report message to a moderator

Re: day,month year regex [message #600299 is a reply to message #600297]

Mon, 04 November 2013 14:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

This is yet another example of the problems which occur when DATE values are actually stored as a string.

Report message to a moderator

Re: day,month year regex [message #600307 is a reply to message #600299]

Mon, 04 November 2013 14:57

babhuko
Messages: 49
Registered: December 2011
Location: Chennai

Member

Is there anyway to handle this, because the source is clob and there is noway I can do any conversion. Regex to identify data, start and end position of all, If 5 times data occurs, I have to identify the start,end positions of all the 5 date patterns. I could not get it using REGEXP_SUBSTR,REGEXP_INSTR, Please give some help.

Report message to a moderator

Re: day,month year regex [message #600308 is a reply to message #600307]

Mon, 04 November 2013 15:03

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I still don't understand what you have (OK, it is some text which contains "date" values). You mentioned "5 patterns". Which patterns are they? Could you provide some sample data (i.e. input) as well as the result you'd like to get from it (i.e. output)?

Report message to a moderator

Re: day,month year regex [message #600309 is a reply to message #600308]

Mon, 04 November 2013 15:35

babhuko
Messages: 49
Registered: December 2011
Location: Chennai

Member

Lets say, I have a data like
Tuesday, October 08, 2013 5:58:30 AM xxx xxxx xxx xxx
xxxx xxxxxx xxxxx xxxx xxx xxxxxx xxxxxxxx xxxxxxxxxxxxxx xxx
Wednesday, October 09, 2013 6:58:30 AM xxxxxxxxxxxxx xxx
xxxxxxxxxxxxxxxxxxxxx
xxxxxxxxx
Wednesday, October 09, 2013 03:58:30 PM

I need to get the below as output

output 1

Tuesday, October 08, 2013 5:58:30 AM
Wednesday, October 09, 2013 6:58:30 AM
Wednesday, October 09, 2013 03:58:30 PM

Note: I have removed all the carriage return, line feed, multiple spaces with single space in the string data. But here I dint give like that and gave as like the data comes.

[Updated on: Mon, 04 November 2013 19:28]

Report message to a moderator

Re: day,month year regex [message #600366 is a reply to message #600309]

Tue, 05 November 2013 07:28

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

SCOTT@orcl > with t as (
  2             select 'Tuesday, October 08, 2013 5:58:30 AM xxx xxxx xxx xxx' str from dual union all
  3             select 'xxxx xxxxxx xxxxx xxxx xxx xxxxxx xxxxxxxx xxxxxxxxxxxxxx xxx' from dual union all
  4             select 'Wednesday, October 09, 2013 6:58:30 AM xxxxxxxxxxxxx xxx' from dual union all
  5             select 'xxxxxxxxxxxxxxxxxxxxx ' from dual union all
  6             select 'xxxxxxxxx' from dual union all
  7             select 'Wednesday, October 09, 2013 03:58:30 PM' from dual
  8            )
  9  select  str,
 10          regexp_substr(
 11                        str,
 12                        '^(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday), ' ||
 13                        '(January|February|March|April|May|June|July|August|September|October|November|December) ' ||
 14                        '\d{1,2}, \d{4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)'
 15                       ) dt
 16    from t
 17  /

STR                                                           DT
------------------------------------------------------------- ---------------------------------------------------------
Tuesday, October 08, 2013 5:58:30 AM xxx xxxx xxx xxx         Tuesday, October 08, 2013 5:58:30 AM
xxxx xxxxxx xxxxx xxxx xxx xxxxxx xxxxxxxx xxxxxxxxxxxxxx xxx
Wednesday, October 09, 2013 6:58:30 AM xxxxxxxxxxxxx xxx      Wednesday, October 09, 2013 6:58:30 AM
xxxxxxxxxxxxxxxxxxxxx
xxxxxxxxx
Wednesday, October 09, 2013 03:58:30 PM                       Wednesday, October 09, 2013 03:58:30 PM

6 rows selected.

SCOTT@orcl >

SY.

[Updated on: Tue, 05 November 2013 07:29]

Report message to a moderator

Re: day,month year regex [message #600371 is a reply to message #600366]

Tue, 05 November 2013 08:21

babhuko
Messages: 49
Registered: December 2011
Location: Chennai

Member

Thanks solomon and hi to all,

str is a clob column.

When I give as below I get the result "Tuesday, October 08, 2013 5:58:30 AM"

regexp_substr(str,'^(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday), ' ||
'(January|February|March|April|May|June|July|August|September|October|November|December) ' ||
'\d{1,2}, \d{4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)',1,1)

But when I give

regexp_substr(str,'^(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday), ' ||
'(January|February|March|April|May|June|July|August|September|October|November|December) ' ||
'\d{1,2}, \d{4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)',1,2)

Am not getting the second value instead am getting NULL as result, same applies for 1,3. Please let me know what I am missing here.

[Updated on: Tue, 05 November 2013 08:55]

Report message to a moderator

Re: day,month year regex [message #600375 is a reply to message #600371]

Tue, 05 November 2013 11:36

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

You need to read docs to understand REGEXP:

◦'m' treats the source string as multiple lines. Oracle interprets the caret (^) and dollar sign ($) as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then Oracle treats the source string as a single line.

Since you are looking dor dates at the beginning of a line you need to use match parameter m:

SCOTT@orcl > with t as (
  2             select 'Tuesday, October 08, 2013 5:58:30 AM xxx xxxx xxx xxx
  3  xxxx xxxxxx xxxxx xxxx xxx xxxxxx xxxxxxxx xxxxxxxxxxxxxx xxx
  4  Wednesday, October 09, 2013 6:58:30 AM xxxxxxxxxxxxx xxx
  5  xxxxxxxxxxxxxxxxxxxxx
  6  xxxxxxxxx
  7  Wednesday, October 09, 2013 03:58:30 PM' str from dual
  8            )
  9  select  regexp_substr(
 10                        str,
 11                        '^(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday), ' ||
 12                        '(January|February|March|April|May|June|July|August|September|October|November|December) ' ||
 13                        '\d{1,2}, \d{4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)'
 14                       ) dt1,
 15          regexp_substr(
 16                        str,
 17                        '^(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday), ' ||
 18                        '(January|February|March|April|May|June|July|August|September|October|November|December) ' ||
 19                        '\d{1,2}, \d{4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)',
 20                        1,
 21                        2,
 22                        'm'
 23                       ) dt2,
 24          regexp_substr(
 25                        str,
 26                        '^(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday), ' ||
 27                        '(January|February|March|April|May|June|July|August|September|October|November|December) ' ||
 28                        '\d{1,2}, \d{4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)',
 29                        1,
 30                        3,
 31                        'm'
 32                       ) dt3
 33    from  t
 34  /

DT1                                  DT2                                    DT3
------------------------------------ -------------------------------------- ---------------------------------------
Tuesday, October 08, 2013 5:58:30 AM Wednesday, October 09, 2013 6:58:30 AM Wednesday, October 09, 2013 03:58:30 PM

SCOTT@orcl >

If you don't care if date starts at the beginning of the line:

SCOTT@orcl > with t as (
  2             select 'Tuesday, October 08, 2013 5:58:30 AM xxx xxxx xxx xxx
  3  xxxx xxxxxx xxxxx xxxx xxx xxxxxx xxxxxxxx xxxxxxxxxxxxxx xxx
  4  Wednesday, October 09, 2013 6:58:30 AM xxxxxxxxxxxxx xxx
  5  xxxxxxxxxxxxxxxxxxxxx
  6  xxxxxxxxx
  7  Wednesday, October 09, 2013 03:58:30 PM' str from dual
  8            )
  9  select  regexp_substr(
 10                        str,
 11                        '(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday), ' ||
 12                        '(January|February|March|April|May|June|July|August|September|October|November|December) ' ||
 13                        '\d{1,2}, \d{4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)'
 14                       ) dt1,
 15          regexp_substr(
 16                        str,
 17                        '(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday), ' ||
 18                        '(January|February|March|April|May|June|July|August|September|October|November|December) ' ||
 19                        '\d{1,2}, \d{4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)'
 20                       ) dt2,
 21          regexp_substr(
 22                        str,
 23                        '(Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday), ' ||
 24                        '(January|February|March|April|May|June|July|August|September|October|November|December) ' ||
 25                        '\d{1,2}, \d{4} \d{1,2}:\d{1,2}:\d{1,2} (AM|PM)'
 26                       ) dt3
 27    from  t
 28  /

DT1                                  DT2                                  DT3
------------------------------------ ------------------------------------ ------------------------------------
Tuesday, October 08, 2013 5:58:30 AM Tuesday, October 08, 2013 5:58:30 AM Tuesday, October 08, 2013 5:58:30 AM

SCOTT@orcl >

SY.

Report message to a moderator

Previous Topic:	How to update data from backup table
Next Topic:	what array or collection type to use

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 06:56:54 CDT 2024