1

String contains words separated by spaces.

How to get substring from start until first uppercase word (uppercase word excluded)? If string start with uppercase, this word should included. Search should start from secord word. First word should always appear in result.

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 Õ, Ä, Ö , Ü, Š, Ž

Andrus
  • 26,339
  • 60
  • 204
  • 378

2 Answers2

1

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

nbk
  • 45,398
  • 8
  • 30
  • 47
  • This returs wrong result if uppercase character is after first charader in word. `SELECT substring('aaa b cD Dfff dfgdf' from '^[^A-ZÕÄÖÜŠŽ]*')` returns `aaa b c` but should return `aaa b cD`. How to fix it? – Andrus Dec 03 '22 at 20:58
  • then you need to change it a bit, but as you add more and more conditions you will get more and more complexity – nbk Dec 03 '22 at 21:11
  • Question was not clear. Removal should start from second word. If string starts with upper case, first word should appear in result. I updated question. How to start removal from second word? – Andrus Dec 05 '22 at 13:07
  • my query does exactly what you wanted, even with the the new string https://dbfiddle.uk/lIQlIcGK – nbk Dec 05 '22 at 14:33
  • How to add exception that that word T-shirt is included in result? Eq Product T-shirt Adidas should return Product T-shirt? – Andrus Dec 06 '22 at 14:21
  • you can't raise an exception in sql, you can do this only procdures, you can always check if the result has a position > 0 for t-shirt – nbk Dec 06 '22 at 16:05
  • I want simply that word T-shirt will appear in result also. How to change your query so word T-shirt will not break result but other words staring uppercase will break? I dont want to raise any exception, only change result. – Andrus Dec 06 '22 at 21:20
  • @Andrus my query does exact what you want https://dbfiddle.uk/ldBdKhG3 – nbk Dec 06 '22 at 21:37
  • It returs worng result for Product Tshirt Adidas . It should return Product in this case. I posted this as separate question in https://stackoverflow.com/questions/74709269/how-get-name-before-first-uppercase-word-excluding-t-shirt – Andrus Dec 06 '22 at 21:40
1

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

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

See live demo.

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

(?<!^) is a negative look behind asserting that the match is not preceded by start of input.

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).

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Question was not clear. Removal should start from second word. If string starts with upper case, first word should appear in result. I updated question. How to start removal from second word? – Andrus Dec 05 '22 at 13:06
  • 1
    @Andrus Solved. See edit to answer. – Bohemian Dec 05 '22 at 16:07
  • Thank you. Great. How to add exception that that word **T-shirt** is included in result? Eq __Product T-shirt long Adidas__ should return **Product T-shirt long** ? – Andrus Dec 06 '22 at 14:18
  • That would be changing the nature of the question, which would invalidate current answers. Please [ask a new question](https://stackoverflow.com/questions/ask). – Bohemian Dec 06 '22 at 19:04
  • I posted new question in https://stackoverflow.com/questions/74709269/how-get-name-before-first-uppercase-word-excluding-t-shirt – Andrus Dec 06 '22 at 21:33