Search code examples
mysqlsqlsql-like

Mysql Select Like query

I am a beginner in SQL. I wanted to show all the rows that has 'P1' but my query not giving me my desired output I have table named 'tbl1' like this

tbl1

id          Name           Tag
8756        ADD            P2;P3;P10;P11;P12;P15
7861        Add1           P6;P4;P5;P2;P3;P1
7823        Place          P11;P12;P10;P8;P9;P6;P7;P4;P5;P2;P3
5567        Tun            P12;P10;P8;P9;P1;P6;
6789        lac            P3;

My query is like this

SELECT id,name,tag FROM tbl1 WHERE tag LIKE '%P1%'

but my output is like this 

id          Name           Tag
8756        ADD            P2;P3;P10;P11;P12;P15
7861        Add1           P6;P4;P5;P2;P3;P1
7823        Place          P11;P12;P10;P8;P9;P6;P7;P4;P5;P2;P3
5567        Tun            P12;P10;P8;P9;P1;P6;

my desired output is only those who has p1 which should only be this two only. is it possible?

 id          Name           Tag
 7861        Add1           P6;P4;P5;P2;P3;P1
 5567        Tun            P12;P10;P8;P9;P1;P6;
Solution

  • Your like is matching P11 and P12 etc as well as P1

    You can do the following things, first look for ;P1; rather than P1

    Second, fix the edge cases (where the tag is first of last). something like:

    SELECT id,name,tag FROM tbl1 WHERE concat(';',tag,';') LIKE '%;P1;%'