1

I'm trying to run a mass deletion of data in a table based on the datestamp. However, the datestamp is written to the table along with a load of other stuff and the pattern varies so I can't use SUBSTR or any similar string manipulation functions, and I think my only option is to use REGEXP_SUBSTR. Unfortutaley, I'm not familiar with REGEXP syntax and I'm not having much luck.

Basically, my string contains the date in the following string:

GKKPACT  -  30-MAY-2014:16:39:13
GKKPACT  -  30-MAY-2014:16:39:13
GKKPACT  -  30-MAY-2014:16:39:13
v_chldcnt - 30-MAY-2014:16:39:26
v_chldcnt - 30-MAY-2014:16:39:26

I just need to extract the dates from the strings above so I can perform a deletion based on sysdate.

Any ideas what the regexp syntax might be to do this? I've found a similar request on stack overflow (Oracle - extract timestamp from varchar string?) - I have tried amending the syntax from this example but haven't succeeded in getting it to work.

TIA

Huskie.

Community
  • 1
  • 1
Huskie69
  • 589
  • 2
  • 7
  • 25

1 Answers1

1

By extract, do you mean something like:

DECLARE
    match VARCHAR2(255);
BEGIN
    match := REGEXP_SUBSTR(subject, '\d{2}-\w{3}-\d{4}', 1, 1, 'im');
END;

Explain Regex

\d{2}                    # digits (0-9) (2 times)
-                        # '-'
\w{3}                    # word characters (a-z, A-Z, 0-9, _) (3
                         # times)
-                        # '-'
\d{4}                    # digits (0-9) (4 times)
zx81
  • 38,175
  • 8
  • 76
  • 97
  • That works! I've not used the bind variable though, just the regexp code you provided. What does the '1, 1, 'im' at the end of the regexp do? I've removed them and get the same results. – Huskie69 Jun 02 '14 at 11:19
  • You're right, there's no need, that's just my default set up (case-insensitive = `i`, multi-line = `m`...) – zx81 Jun 02 '14 at 11:23
  • Great - I've added to my long list of invaluable code snippets! Thanks again. – Huskie69 Jun 02 '14 at 11:28
  • `added to my long list of invaluable code snippets` In that case I highly recommend you consider [this](http://stackoverflow.com/questions/23589174/match-or-replace-a-pattern-except-in-situations-s1-s2-s3-etc/23589204#23589204) for your collection. :) And you're very welcome, it was a pleasure. – zx81 Jun 02 '14 at 11:41