CODEWITHSUNDEEP
regexoracle-databasedateplsqltimestamp
Huskie69
Huskie69

Reputation: 865

Extract Date from VARCHAR string ORacle

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.

Upvotes: 1

Views: 3302

Answers (1)

zx81
zx81

Reputation: 41838

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)

Upvotes: 1

Related Questions