Substring before first uppecase word-CodePudding

String contains words separated by spaces.

How to get substring from start until first uppercase word (uppercase word excluded)? For example

select substringtiluppercase('aaa b cc Dfff dfgdf')

should return

aaa b cc

Can regexp substring used or other idea?

Using PostgreSQL 13.2

Uppercase letters are latin letters A .. Z and additionally Õ, Ä, Ö , Ü, Š, Ž

CodePudding user response：

Sunstring supprts Regular expüression in Postgres

SELECT substring('aaa b cc Dfff dfgdf' from '^[^A-ZÕÄÖÜŠŽ]*')

substring
aaa b cc

SELECT 1

fiddle

SELECT 
reverse(substr(reverse(substring('aaa b ccD Dfff dfgdf' from '.*\s[A-ZÕÄÖÜŠŽ]')),2))

reverse
aaa b ccD

SELECT 1

fiddle

CodePudding user response：

Replace everything from a leading word boundary then an uppercase letter onwards with blank:

regexp_replace('aaa b cc Dfff dfgdf', '\m[A-ZÕÄÖÜŠŽ].*', '')

In Postgres flavour of regex, \m "word boundary at the beginning of a word"

fyi the other Postgres word boundaries are \M at end of a word, \y either end (same as usual \b) and \Y not a word boundary (same as usual \B).