Oracle String Column Reformat Using RegExp Replacement

This post discusses how to quickly reformat a string column in oracle table.

Sample table definition goes as follows:

CREATE TABLE test (event_id VARCHAR2(20));
INSERT ALL
INTO test VALUES (‘2015-06-25-000122’)
INTO test VALUES (‘2015-06-25-000130’)
SELECT * FROM dual;

Now I want to reformat the string format from yyyy-mm-dd-xxxxxx to yyyymmdd-xxxxxx, I can use regular expression as follows:

UPDATE test SET event_id=REGEXP_REPLACE(event_id, ‘([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})-([[:digit:]]{6})’, ‘\1\2\3-\4’);

A brief explanation is as follows:

event_id: source chars

‘([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})-([[:digit:]]{6})’: pattern

‘\1\2\3-\4’: replace string

[[:digit]] stands for any digit combinations

{num} specify how many digits are there

() in the pattern string is used to specify a trunk of string in the pattern so that we can use number in the replace string to refer to the trunk. For example, ‘\1’ in the replace string ‘\1\2\3-\4’ refers to the ([[:digit:]{4}) in the pattern string.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s