Search code examples
sqlregexpostgresqlsubstringregexp-replace

How get name before first uppercase word excluding T-shirt


Products have names like

Product one white Adidas
Other product black Hill sheet
Nice T-shirt blue Brower company

How to get starting part of product name before first uppercase word starting from second word and up to first uppercase word excluding word T-shirt. Result from strings above should be

Product one white
Other product black
Nice T-shirt blue

Using Bohemian answer to question

Substring before first uppecase word excluding first word

regexp_replace('Nice T-shirt blue Brower company', '(?<!^)\m[A-ZÕÄÖÜŠŽ].*', '')

returns wrong result

Nice

How to modify regex so that it returns

Nice T-shirt blue

Using Postgres 12


Solution

  • Use a negative look ahead:

    select regexp_replace('Nice T-shirt blue Brower company', '(?<!^)\m(?!T-shirt)[A-ZÕÄÖÜŠŽ].*', '')
    

    See live demo.

    (?!T-shirt) means the following characters must not be 'T-shirt'

    You can add other capitalised terms to ignore:

    (?!T-shirt|BB gun|BBQ tongs)