SELECT in MariaDB 10.3 which finds all records with and without spaces

Question

I have a query for SELECT in MariaDB 10.3, which finds all records with and without spaces (accepting accents at the same time). Spaces can be anywhere (after each character, for example).

If I have in the database in the column 'code' value: ŘCT 111 222 33 33

So I want it to find me the code by entering with spaces ŘCT 111 222 33 33 and without spaces ŘCT1112223333.
At the same time, I need it to accept diacritics, ie. to find the code by entering both with the diacritics ŘCT1112223333 and without RCT1112223333.

I call SELECT in PHP, so I do chunk_split_unicode() (see below) first and then SELECT with RLIKE, but it does not accept diacritics, if I enter only RCT1112223333, then it will not find anything.

Any easier way?

I will be happy for any advice. Thank you so much.

Martin

$text = "RCT1112223333";
$param = chunk_split_unicode($text, 1, ' ?');
$query = "SELECT * FROM Data WHERE code RLIKE " . $param;

public static function chunk_split_unicode($str, $l = 76, $e = "\r\n") {
        if (is_null($str)) {
            $str = "";
        }
        $tmp = array_chunk(preg_split("//u", $str, -1, PREG_SPLIT_NO_EMPTY), $l);
        $str = "";
        foreach ($tmp as $t) {
            $str .= join("", $t) . $e;
        }
        // jelikoz se v SQL pouzivaji REGEXP, tak je nutne vyescapovat znaky pouzivane v reg. vyrazech
        $str = str_replace(array('(', ')', '[', ']', '{', '}', '*' , '+'),  array('\(', '\)', '\[', '\]', '\{', '\}', '\*', '\+'), $str);
        return $str;
    }

Edit: SQL query

SQL query SHOW CREATE TABLE data is:

CREATE TABLE `data` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `code` varchar(250) NOT NULL DEFAULT '',
 `name` varchar(250) NOT NULL DEFAULT '',
 `description` varchar(250) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4282 DEFAULT CHARSET=utf8

So if the data is like this https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=95ad09fbc590624533b23829be9af71d , you want to be able to look for all of them by searching either one of the variation? — FanoFN, Feb 08 '22 at 09:39
I don't know what MariaDB capabilities are but this could use a function-based index or a virtual column to host a normalised version of the string. — Álvaro González, Feb 08 '22 at 09:48
Might be able to get the result using `REGEXP_REPLACE()`.. however the character with accents `Ř` is a bit of a challenge to get around. Please run `SHOW CREATE TABLE data;` and post the result into your question. — FanoFN, Feb 09 '22 at 00:14
Maybe there's a way to replace the accented characters in PHP then use that value to do search in MariaDB? — FanoFN, Feb 09 '22 at 00:22
I've found a related question here: https://stackoverflow.com/questions/4813620/how-to-remove-accents-in-mysql Maybe you can use the answers in this thread to come up with something. I'd first add a column to provide the search function with correct data, because changing things on the fly take time... — Marty McVry, Feb 09 '22 at 10:06

SELECT in MariaDB 10.3 which finds all records with and without spaces

0 Answers0