Question 90665 in PostgreSQL Question posted in
The official documentation can be found here.

How get name before first uppercase word excluding T-shirt – Postgresql

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

Tags:

3

Answers


  1. 0

    This regex works for your test cases:

    ^[A-Z][a-z ]*(T-shirt)?[a-z ]*

    Explanation:

    ^: Start of line

    [A-Z]: Any capital letter

    [a-z ]*: zero or more characters that are either a lowercase letter or space

    (T-shirt)?: The phrase T-shirt 0 or 1 times

    [a-z ]*: same again

    Login or Signup to reply.
  2. 0

    Instead of doing a direct match, you can simply remove the part of the string (in this case from the last uppercase word to the end) that you do not need:

    select regexp_replace(name, '(?<=s)[A-Z]+[a-zA-Zs]+$', '') from tbl

    See fiddle.

    Login or Signup to reply.
  3. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search