I have a MariaDB database (I'm using 10.3, if that's relevant) which contains UTF-8 text.
If I do a string comparison using a collation order of utf8mb4_general_ci, it ignores diacritics (for example SELECT 'é' = 'e'; evaluates to 1 in this collation order). This is useful when implementing some sorts of searches.
However, if I do a similar comparison using the REGEXP operator, the comparison is case insensitive but does not ignore diacritics. Is there a way of getting REGEXP to respect the current collation when comparing characters? (I note the documentation of REGEXP_SUBSTR only says 'the function follows the case sensitivity rules of the effective collation', not that it it fully respects the collation.)
Here's an MWE in case it helps illustrate the issue:
CREATE TABLE place ( name VARCHAR(32) )
DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
INSERT INTO place (name) VALUES ('École'), ('Ecole'), ('ecole');
SELECT * FROM place WHERE name='ecole'; -- Finds all three rows
SELECT * FROM place WHERE name REGEXP 'ecole'; -- Finds just the last two rows
DROP TABLE place;