I have a query which is supposed to find matching rows ignoring case and special characters that may be present both in the query and the corresponding column. For that I use REGEXP_REPLACE like this:

SELECT *
FROM Order
WHERE REGEXP_REPLACE(reference, '[^a-zA-Z0-9äöüÄÖÜ]', '') LIKE %:search%

where search is the name of the parameter I want to use. That works, but doesn't yet sanitize the search parameter from unwanted special characters.

What I would like to do is something like the following, i.e. having the REGEXP_REPLACE on the right side as well:

SELECT *
FROM Order
WHERE REGEXP_REPLACE(reference, '[^a-zA-Z0-9äöüÄÖÜ]', '') LIKE %REGEXP_REPLACE(:search, '[^a-zA-Z0-9äöüÄÖÜ]', '')%

However that doesn't work and I get the following error:

42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%REGEXP_REPLACE(

Is it not possible to use a function on the parameter or as part of a LIKE statement? Are there any workarounds?

CodePudding user response：

It looks like you want to create a string starting and ending with '%' to use in your LIKE operator. To do that in MySQL's dialect of SQL you need to do your string manipulation explicitly using the built-in string manipulation functions.

You can use those functions anywhere your query needs a text string.

Try using CONCAT in an expression like this to generate that string. You'll be able to use it on the right side of your LIKE.

CONCAT('%', REGEXP_REPLACE(:search, '[^a-zA-Z0-9äöüÄÖÜ]', ''), '%')

I hope you don't want your query to be fast. It will be slow. It must examine every value of Order.reference in your table. It's slow because

1. it's not sargable due to WHERE f(column) LIKE whatever, and
2. column LIKE '%something%' requires looking at every value of column, rather than random-acccessing a BTREE index.

If you build a database to scale up, you design it so your queries can be sargeable. Sargability here might look like

WHERE cleaned_up_reference 
 LIKE CONCAT(REGEXP_REPLACE(:search, '[^a-zA-Z0-9äöüÄÖÜ]', ''), '%')

without the leading % on the right, and without evaluating any function on the column or columns being searched.

CodePudding user response：

You can try this:

SELECT * FROM Order a
WHERE REGEXP_REPLACE(a.reference, '[^a-zA-Z0-9äöüÄÖÜ]', '') LIKE '%:search%'