SQLITE 复杂模糊查询的例子

1、想从1 个表的1个字段（devTids）中查找出包包含字符的记录

devTids字段的示例记录1条：【rfidtid,devcode】

【E2801191200068FC64650304,实验室0.4kv安全带#01】;【E2806894200050010B93C473,123456】;【E2806894200050010B93C476,安全帽#04】;【E28068942000501D5D241D80,实验室10kv接地线#001】;【E28068942000502A4687958D,安全帽#06】;【E28069952000500433F08588,T2实验室0.4kv安全带#01】;【E280F3362000F00000B05094,实验室0.4kv安全帽#01】;

想找出包含devcode包含‘12’的记录 ===>  devTids like '%12%'

表：op_tools_events（目标）

dev_tools 、discard_dev_tools （工具表）

查询sql：

select id,devTids from op_tools_events where id in
(
select DISTINCT id from 
(
select id,substr(word, 2, instr(word, ',')-2) as word1 from (
WITH split(word, str,id) AS (
    select '',devTids,id from op_tools_events where devTids like '%12%'
    UNION all SELECT
    substr(str, 0, instr(str, ';')),
    substr(str, instr(str, ';')+1),
    id
    FROM split WHERE str!=''
) SELECT word,id FROM split WHERE word!=''

)  
WHERE word1 in (
select rfidtid from dev_tools where devcode like '%12%'
UNION
select rfidtid from discard_dev_tools where devcode like '%12%'
) 
)
)
order by id