Mysql exclude percent sign during search - reusable script

Question

I've a VAT TAX TABLE where vats in most of cases have names with percent sign at the end, like:

------------------------------------------
ID  |  Name                |   Value     |
------------------------------------------
 1  |  VAT 22%             |     22      |
 2  |  VAT 10%             |     10      |
 3  |  VAT 23%             |     23      |
 4  |  VAT 10% IND-50%     |     10      |
------------------------------------------

I have a function that return true if a name is already in use during a new inserting (or modifying a row):

function check_name_exist($id, $name) {

    if ( $id == "" ) {

        $sql = "SELECT Name FROM tax WHERE Name LIKE ?";
        try {
            $rs = $db->prepare($sql);
            $rs->execute(array($name));
            $ris = $rs->fetchAll(PDO::FETCH_ASSOC);
        } catch(PDOException $e) {
            die($e);
        }
    } else {

        $sql = "SELECT Name FROM tax WHERE Name LIKE ? AND ID <> ?";
        try {
            $rs = $db->prepare($sql);
            $rs->execute(array($name, $id));
            $ris = $rs->fetchAll(PDO::FETCH_ASSOC);
        } catch(PDOException $e) {
            die($e);
        }
    }

    if ( count($ris) > 0 )
        return true;

    return false;

}

Problem occur when I try to modify ID = 2. It tells me that there is already a row with "VAT 10%". In this case row ID = 4.

But THIS IS AN ERROR.

How can I modify the script to manipulate percent sign passed as char and not as wildcard? (I need to create a universal function also for when i don't have the percent sign in the Name...in general)

why do you need `LIKE` in this situation? What's wrong with `=`? The reason for the error is of course that row 4 also matches `VAT 10` when you use a `LIKE`. But if you just did `= 'VAT 10%'` then it would work fine. I can't quite see why LIKE is required here. — ADyson, Apr 10 '19 at 16:00
If for any reason you need to use `LIKE` then see this answer regarding escaping the % to make it a literal: https://stackoverflow.com/questions/19749787/how-to-use-a-percent-in-a-like-without-it-being-treated-as-a-wildcard?lq=1 — ADyson, Apr 10 '19 at 16:02
Possible duplicate of [How to use a percent (%) in a LIKE without it being treated as a wildcard?](https://stackoverflow.com/questions/19749787/how-to-use-a-percent-in-a-like-without-it-being-treated-as-a-wildcard) (assuming that `LIKE` is actually necessary, which I'm not convinced it is) — ADyson, Apr 10 '19 at 16:16
I don't know I use LIKE :/ .... now with = all work great! thanks — Giuseppe Lodi Rizzini, Apr 11 '19 at 07:00
That's great. Now make sure you take a moment to read documentation and examples so that you understand the different behviour of `LIKE` and `=` in SQL :-) — ADyson, Apr 11 '19 at 07:31

score 0 · Answer 1 · answered Apr 10 '19 at 16:10

0

You could try escaping the % inside the string using \%

 select Name  from tax  where 'VAT 22% ..' like 'VAT 22\%%';;

answered Apr 10 '19 at 16:10

ScaisEdge

131,976
10
91
107

Mysql exclude percent sign during search - reusable script

1 Answers1