1

I tried to make a research but just from prefixe.

Eg. when I search 10542, 110542 shouldn't be there, just 105428001.

The string :

110542004
105428001

I tried

LEFT(INV.INV_CD,5) LIKE '%".$option3."%'";

and

SUBSTRING(INV.INV_CD,1,5) LIKE '%".$option3."%'";

Both result are the same.

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
Beny Bond Banjarnahor
  • 225
  • 1
  • 9
  • 4
    Your first % in the like allows it to start anywhere, remove it to start at the beginning. – Nigel Ren Jul 30 '18 at 15:04
  • Note that with `%` at the start of your string, the optimizer can't use the index on that column *if any* [see this](https://stackoverflow.com/questions/2481528/mysql-like-performance-boost) – Accountant م Jul 30 '18 at 15:07

2 Answers2

1

The problem is your LIKE syntax :

LIKE '%".$option3."%'";

When you use % it means you can have something at the same place, so LIKE '%".$option3."%'"; you can return value with XXXX + $option3 + XXXX (with XXXX equal to nothing or multiple char)

If you only want $option3 + XXXX, just write LIKE '".$option3."%'";

Here is the documentation about this :

With LIKE you can use the following two wildcard characters in the pattern:

% matches any number of characters, even zero characters.

_ matches exactly one character.

Community
  • 1
  • 1
Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
  • Well you have a problem somewhere, but if you have `105428001` and you write your query with `LIKE '105%'` you will return it : here is an example http://sqlfiddle.com/index.html#!9/f0386e/1 – Mickaël Leger Jul 30 '18 at 15:21
1

When you have a % in your like clause, this allows any characters, so one at the begining allows it to start anywhere. Remove it to start at the begning...

INV.INV_CD LIKE '".$option3."%'";

Note: You should also try to use prepared statements as they can stop a lot of problems in the future!

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55