Would like to change to each end of word instead of end of string (matching) - MariaDB/MySQL + REGEXP_REPLACE

Question

Would like to change to each end of word instead of end of string (matching)?

I have this query now:

SELECT REGEXP_REPLACE(name,"(a|o|e|y|cy|dzy|rzy|owie|i|u|ów|owi|em)$",'') FROM `zp_poster`

Example query:

SELECT REGEXP_REPLACE("Józefowi Piłsudski","(a|o|e|y|cy|dzy|rzy|owie|i|u|ów|owi|em)$",'')

Expected result:

Józef Piłsudsk

Real result:

Józefowi Piłsudsk

Answer 1

You can use the word boundaries character class.

As explained in the documentation, starting with version 8.0.4 MySQL regexes rely on ICU notation (International Components for Unicode), where word boundaries are expressed as \b. In earlier versions, Spencer implementation was used, and boundaries were represented as [[:<:]] (before a word) and [[:>:]] (after a word).

SELECT REGEXP_REPLACE(
    name,
    '(a|o|e|y|cy|dzy|rzy|owie|i|u|ów|owi|em)\\b',
    ''
) 
FROM `zp_poster`

Demo on DB Fiddle:

WITH t AS (
  SELECT 'Józefowi Piłsudski' name
  UNION ALL SELECT 'Piłsudski Józefowi'
)
SELECT 
    name,
    REGEXP_REPLACE(
      name,
      '(a|o|e|y|cy|dzy|rzy|owie|i|u|ów|owi|em)\\b',
      ''
    ) replaced
FROM t;

| name               | replaced       |
| ------------------ | -------------- |
| Józefowi Piłsudski | Józef Piłsudsk |
| Piłsudski Józefowi | Piłsudsk Józef |