How to find text pattern in string for postgresql SQL

Question

For this sample string: "... Key Match extra text..."

How do I get the value "Match", which is the string between blank spaces after "Key"?

is there a better way than: Find position of "Key "->pos1, find position of first blank space after p1 -> p2, substring(string, p1,p2)?

This is not working as I expected

Select substring('Key Match extra text', 'Key (.+) ');
---
Match extra

No idea what is better in your case, but _perhaps_ [9.7. Pattern Matching](https://www.postgresql.org/docs/current/functions-matching.html) has a list of options you may want to put into consideration. — hakre, Oct 29 '21 at 15:53

score 1 · Accepted Answer · answered Oct 29 '21 at 16:17

1

You can make the regex be "non-greedy", so that .+ matches as few as possible:

Select substring('Key Match extra text', 'Key (.+?) ');

Or you can change . to something that won't match spaces:

Select substring('Key Match extra text', 'Key (\S+) ');

answered Oct 29 '21 at 16:17

jjanes

1 Answers1