MySQL regex function Turkish character problem

Question

I am searching with php in Turkish. In Turkish alphabet have 'i' and 'ı' lowercase characters. Also have 'I' and 'İ' uppercase characters.

Mysql select query in regex function cannot be found Turkish character, example: İ - ı .. My search column data type character set UTF-8 general_ci.

I am waiting for your answers. Thanks in advance.

Answer 1

You can solve the Turkish character problem in the following ways.

Use the HTML and JQuery code, like below;

function searchBarReplaceChar() {
        searchInput = $.trim($('input[name="replaceChar"]').val());

        let charMap = {
            Ç: '[CÇ]+',
            C: '[CÇ]+',
            Ö: '[OÖ]+',
            O: '[OÖ]+',
            Ş: '[SŞ]+',
            S: '[SŞ]+',
            İ: '[Iİ]+',
            I: '[Iİ]+',
            Ü: '[UÜ]+',
            U: '[UÜ]+',
            Ğ: '[GĞ]+',
            G: '[GĞ]+',
            ç: '[cç]+',
            c: '[cç]+',
            ö: '[oö]+',
            o: '[oö]+',
            ş: '[sş]+',
            s: '[sş]+',
            ı: '[ıi]+',
            i: '[ıi]+',
            ü: '[uü]+',
            u: '[uü]+',
            ğ: '[gğ]+',
            g: '[gğ]+',
        };

        let str_array = searchInput.split('');

        for (let i = 0, len = str_array.length; i < len; i++) {
            str_array[i] = charMap[str_array[i]] || str_array[i];
        }

        searchInput = str_array.join('');

        let replaceChar = searchInput.replace(/[]/gi, "");
        let lastChar = replaceChar.slice(-1);
        if (lastChar === '+') {
            replaceChar = replaceChar.slice(0, -1);
        }
        $('.result').text(replaceChar);
    }

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<input type="text" name="replaceChar" value="şehir bu gece çok güzel" />
<p class="result"></p>
<button type="submit" onclick="searchBarReplaceChar()" >Send</button>

send this result to your php file with ajax post.

later, your php file code,

public function search_regexp() {
    $regexp = str_replace('i', 'İ', $_POST['get_replace_result']);
    $regexp = str_replace('ı', 'I', $regexp);
    $regexp = mb_strtoupper($regexp, "UTF-8");
    $sql_query = "SELECT * FROM your_table WHERE example_column regexp '$regexp'";
    // $sql_query = "SELECT * FROM your_table WHERE example_column regexp '[SŞ]+EH[Iİ]+R B[UÜ]+ [GĞ]+E[CÇ]+E [CÇ]+[OÖ]+K [GĞ]+[UÜ]+ZEL'";
}

do not forget this, mysql database in your table column data type character set utf8_turkish_ci

Good luck with