CODEWITHSUNDEEP
postgresql
Mio
Mio

Reputation: 1502

Substring a multiline with PostgreSQL

I have one text column that have this type of value

---
cart_id: 23332251
id: 1824
push: 
key: 
last_checked_in_at: 2019-03-04 10:39:53.946659000 Z
other_field: hello

I want to have a column with only last_checked_in_at and extract the value as 2019-03-04 10:39:53.946659000

I tried

SELECT substring(object FROM '.*last_checked_in_at: (.*) Z') AS last_checkin_at, *
FROM versions;

But I get the begging of the field but it never stop and take the rest of the lines below.

I tried substring(object FROM '.*last_checked_in_at: (.*) Z$') but I get NULL. I am on PostgreSQL 10.

Upvotes: 0

Views: 1454

Answers (1)

unutbu
unutbu

Reputation: 879103

Given

CREATE TABLE test (
    object text
);
INSERT INTO test VALUES
($$---
cart_id: 23332251
id: 1824
push: 
key: 
last_checked_in_at: 2019-03-04 10:39:53.946659000 Z
other_field: hello$$);

the SQL

SELECT substring(object FROM '.*last_checked_in_at: (.*) Z') AS last_checkin_at, * FROM test;

yields

+-------------------------------+-----------------------------------------------------+
|        last_checkin_at        |                       object                        |
+-------------------------------+-----------------------------------------------------+
| 2019-03-04 10:39:53.946659000 | ---                                                +|
|                               | cart_id: 23332251                                  +|
|                               | id: 1824                                           +|
|                               | push:                                              +|
|                               | key:                                               +|
|                               | last_checked_in_at: 2019-03-04 10:39:53.946659000 Z+|
|                               | other_field: hello                                  |
+-------------------------------+-----------------------------------------------------+

So substring(object FROM '.*last_checked_in_at: (.*) Z') works as desired. If you don't want the second column, use

SELECT substring(object FROM '.*last_checked_in_at: (.*) Z') AS last_checkin_at FROM test;

Upvotes: 1

Related Questions