sqlite

Search tag between tags after splited string


I'm using SQLite and PDO for run some SQL commands.

table fields and data:

ID      title       tag           pcode
----------------------------------------
1       title1      aa,bb,cc,dd   a100
2       title2      cc,ee,dd,ba   b250
3       title3      dd,ff,gr,ax   br10
4       title4      cc,dd,aa,fx   q200
5       title5      xx,ad,fr,aa   ar50

I just want separate current field tag value with , character and then search between all tags exists in tag field except current pcode value

As an example: I want to find all tags like as cc or dd or aa or fx with current record and when current pcode is q200 with tag content cc,dd,aa,fx except pcode q200

I did try it with this:

select title,pcode from tblproduct WHERE (tag like '%cc%') and (pcode <> 'q200')

It works, but how can I change it to find all values from current tag, meaning:

Search cc and then search dd and then search aa and then search fx value inside all tags in table except pcode with q200


Solution

  • thanks for all helps, temporary i used this way just with change gr300 to any product code as i want or change to variable,good luck.

    WITH RECURSIVE ctags AS (
    SELECT
        id,
        producttitle,
        tags,
        productcode,
        instr(tags, ',') AS pos,
        substr(tags, 1, instr(tags, ',') - 1) AS tag_value,
        substr(tags, instr(tags, ',') + 1) AS rest
    FROM
        tblproduct
    WHERE
        productcode = 'gr300'
    
    UNION ALL
    
    SELECT
        id,
        producttitle,
        tags,
        productcode,
        instr(rest, ',') AS pos,
        CASE
            WHEN instr(rest, ',') = 0 THEN rest
            ELSE substr(rest, 1, instr(rest, ',') - 1)
        END AS tag_value,
        CASE
            WHEN instr(rest, ',') = 0 THEN ''
            ELSE substr(rest, instr(rest, ',') + 1)
        END AS rest
    FROM
        ctags
    WHERE
        rest <> ''
    )
    
    SELECT DISTINCT
        p.id,
        p.producttitle,
        p.productcode,
        p.tags
    FROM
        tblproduct p
    JOIN
        ctags t ON p.tags LIKE '%' || t.tag_value || '%' AND p.productcode <> 'gr300'