PostgreSQL: Extract one word after a keyword from string

Question

I have a column in Postgres like below and I want to split only the character that comes next to the keyword. In the below case the keyword is

"Patient"

Patient Mark has tested positive
New update for Patient Wilson 
Discharged - Patient Thompson

The result column I need should be like

Mark
Wilson
Thompson

None of your sample values contain the keyword `product` - did you mean `Patient`? — , Jul 08 '20 at 10:32

score 1 · Answer 1 · answered Jul 08 '20 at 10:49

1

Assume your data is in column called str

try following query

SELECT 
  substring(split_part(str, 'Patient', 2) from '[^ ]+'::text) as patient_name 
FROM 
  table_name
;

answered Jul 08 '20 at 10:49

axnet

5,146
3
25
45

Getting error while executing the query Reason: SQL Error [XX000]: ERROR: Not implemented Detail: ----------------------------------------------- error: Not implemented code: 1001 context: 'false' - Function substring(text,text) not implemented - use REGEXP_SUBSTR instead query: 75899630 location: cg_expr_fn_builder.cpp:4510 process: padbmaster [pid=31188] – Vivo Jul 08 '20 at 15:11
Its working, check this fiddle, https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=ad3bb3cd290896ebeda2978ff678cbda – axnet Jul 08 '20 at 17:01

Slava Rozhnev · Answer 2 · 2020-07-08T12:06:24.027

You can use regexp_matches function. For example :

SELECT regexp_matches('Patient Mark has tested positive', 'Patient (\S+)');
 regexp_matches 
----------------
 {Mark}
(1 row)

SELECT regexp_matches('New update for Patient Wilson', 'Patient (\S+)');
 regexp_matches 
----------------
 {Wilson}
(1 row)

SELECT regexp_matches('Discharged - Patient Thompson', 'Patient (\S+)');
 regexp_matches 
----------------
 {Thompson}
(1 row)

In this case regular expression 'Patient (\S+)' returns all not space characters follows after keyword Patient until next space or end of string

PostgreSQL: Extract one word after a keyword from string

2 Answers2