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 has a list of options you may want to put into consideration. — hakre
– hakre, Commented Oct 29, 2021 at 15:53

jjanes · Accepted Answer · 2021-10-29 16:17:04Z

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, 2021 at 16:17

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

1 Comment

The question mark did the trick. Thanks.