postgresql - 使用 PostgreSQL 对多行进行子串化

Question

我有一个文本列具有这种类型的值

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

我想有一列只有last_checked_in_at并将值提取为2019-03-04 10:39:53.946659000

我试过

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

但是我得到了现场的乞求，但它从未停止并采取下面的其余行。

我试过了substring(object FROM '.*last_checked_in_at: (.*) Z$')，但我得到了 NULL。我在 PostgreSQL 10 上。

标签： postgresql

Answer 1

给定

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$$);

SQL

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

产量

+-------------------------------+-----------------------------------------------------+
|        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                                  |
+-------------------------------+-----------------------------------------------------+

所以substring(object FROM '.*last_checked_in_at: (.*) Z')可以按需要工作。如果您不想要第二列，请使用

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