Question 20840 in Mysql Question posted in
The official MySQL documentation can be found here.

how Searching the database for similar numbers? – Mysql

What is the way to search the database (mysql/php code) for the following entries:

  • 123XX
  • 123XY
  • XYZ44
  • 1X344
  • 1Z344
  • Z23YY
  • The input letters are only X – Y – Z and the numbers from 0 to 9

They are all one number, which is (12344), so how can I show these results? The goal is to search for repeated entries.

Another example :

12XYY

X = 3,4,5,6,7,8,9,0

Y = 3,4,5,6,7,8,9,0

Provided that y is not equal to x or any apparent number (1,2)

And X is not equal to Y or any apparent number (1,2)

$number = "1XZYY";
$rnumber = str_replace(array('Y','X','Z'), "ـ", $number);
$lenNumber = strlen(5);

$duplicate = $mysqli->query("SELECT `number` FROM `listNumber` WHERE (length(`number`) = '$lenNumber' && `number` LIKE '%$rnumber%') OR (length(`number`) = '$lenNumber' && `number`LIKE '%$rnumber%')");

I tried many methods, but it was very slow in showing the results because I put the loop inside a loop to search for every number in the first loop

Tags:

2

Answers


  1. 0

    I would use PHP and occupy each digit into the correct position until I find a conflict. To prevent a double loop I use a dictionary helper object to hold values of X, Y and Z.

    
function match_str_to_number($str, $number)
{
    if (strlen($number) != strlen($str)) {
        return false;
    }

    $dict = ['X' => -1, 'Y' => -1, 'Z' => -1];
    for ($i = 0; $i < strlen($number); $i++) {
        if ($number[$i] != $str[$i]) {

            // a number mismatch
            if (!isset($dict[$str[$i]])) {
                return false;
            }

            // a wildcard variable conflict
            if ($dict[$str[$i]] != $number[$i] && $dict[$str[$i]] != -1) {
                return false;
            };

            $dict[$str[$i]] = $number[$i];
        }
    }

    return true;
}

echo match_str_to_number("XYZ44", "12344") ? "true" : "false";
echo match_str_to_number("XYZ4X", "12344") ? "true" : "false";

// output: truefalse
    Login or Signup to reply.
  2. 0

    I understand you want to look for 12344, but some of the digits may be been redacted and replaced with a random capital letter in XYZ. For that, you can use a regular expression:

    WHERE REGEXP_LIKE(foo, '^[XYZ1][XYZ2][XYZ3][XYZ4][XYZ4]$')

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search