Change date format in a varchar column in PostgreSQL 9.3

Question

I have a PostgreSQL database with a information table containing comments about people.

An example is Mr X, born the 18/12/1953 in Y...

I would like change the date's format to ISO 8601 (YYYY-MM-DD) with a unique PostgreSQL request or method.

I know how to detect the dates in my database: '(\d{2})/(\d{2})/(\d{4})'.

What is the best way to replace using this pattern?

I thought about a regex_replace mixed with a split but it's seems too complicated, I'm sure there is a simpler way.

If someone has a good tip, I take it!

Thanks

Is the date format persistent ? Is there any other non-date data having similar pattern ? — Rahul
– Rahul, Commented May 10, 2017 at 15:43
Have you tried anything to solve the task yet? Any patterns with regexp_replace? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 10, 2017 at 15:44

Wiktor Stribiżew · Accepted Answer · 2017-05-10 21:29:15Z

1

Let's assume you have XX/XX/XXXX date-like substrings in your input that is always a date. Then, what you may use is a single REGEXP_REPLACE with a pattern containing capturing groups with corresponding backreferences in the replacement pattern, arranged in the required order and with desired separators:

REGEXP_REPLACE(s, '(\d{2})/(\d{2})/(\d{4})', '\3-\2-\1', 'g')

NOTE: you may enclose the whole pattern with \y, a word boundary, if you need to match those strings as whole words, '\y(\d{2})/(\d{2})/(\d{4})\y'. \d{2} matches two digits, \d{4} matches four digits. (...) creates a numbered capturing group (starting with ID 1 from left to right) and their contents are referenced with \n backreferences. 'g' replaces all occurrences in the string.

An online test:

CREATE TABLE table1
    (s character varying)
;

INSERT INTO table1
    (s)
VALUES
    ('Mr X, born the 18/12/1953 in Y...'),
    ('Mr Y, died the 18/12/2053 in Y...')
;
select REGEXP_REPLACE(s, '\y(\d+)/(\d+)/(\d+)\y', '\3-\2-\1', 'g') as Results from table1;

answered May 10, 2017 at 21:29

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pawus Over a year ago

Thanks a lot ! I did not know the number capture group system, I just had the regex pattern.

Wiktor Stribiżew Over a year ago

What was the pattern? Please share. My answer could be more helpful then.

Pawus Over a year ago

And I confirm that there are only dates in this format in my table. But just to know, changing the regex pattern to capture date formats would solve the case, right? Something like : REGEXP_REPLACE(s, '\y(<Day format>)/(<Month format>)/(<Year format>)\y', '\3-\2-\1', 'g')

Wiktor Stribiżew Over a year ago

I see I used a simplified regex in the demo. Actually, there is no construct for "day format" or "month format" in regex, it just matches (sequences of) chars of specific type/sets, that is all. You may make the date regex more precise, say, limiting the day value to 1-31 and month part to only match 1-12 with \y([012]?[0-9]|3[01])/(0?[1-9]|1[012])/(\d{4})\y. However, it will accept Feb 29, 30, 31, and Sept 31, etc. Still, it is "good-enough".

Pawus Over a year ago

I had "(\d{2})/(\d{2})/(\d{4})". (Without the two '\y') I knew how to detect them, not how to replace. It's perfect, I just have to create my UPDATE request now.

Collectives™ on Stack Overflow

Change date format in a varchar column in PostgreSQL 9.3

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related