[760]sqllite中实现字符串分割

• 2020 年 2 月 17 日
• 笔记

sqlite 从固定字符串分隔

WITH split(word, str) AS (      -- alternatively put your query here      -- SELECT '', category||',' FROM categories      SELECT '', 'Auto,A,1234444,dsfdd,sd,sd,77'||','      UNION ALL SELECT      substr(str, 0, instr(str, ',')),      substr(str, instr(str, ',')+1)      FROM split WHERE str!=''  ) SELECT word FROM split WHERE word!='';

sqlite 从表中获取数据

WITH split(word, str) AS (      SELECT '', (select chi_sht from tr_map_org_basic where com_name_en='code cat' and status=1)||','      UNION ALL SELECT      substr(str, 0, instr(str, ',')),      substr(str, instr(str, ',')+1)      FROM split WHERE str!=''  ) SELECT word FROM split WHERE word!='';

sqlite 从表中获取数据，union all 连接语句

WITH split(word, str) AS (      SELECT '', (select chi_sht from tr_map_org_basic where com_name_en='code cat' and status=1)||','      UNION ALL SELECT      substr(str, 0, instr(str, ',')),      substr(str, instr(str, ',')+1)      FROM split WHERE str!=''  ) SELECT word FROM split WHERE word!=''  union all select eng_sht from tr_map_org_basic where com_name_en='code cat';

参考：https://blog.csdn.net/weixin_30381793/article/details/98222363

https://blog.csdn.net/yannie3321/article/details/82153747 https://blog.csdn.net/zeeeitch/article/details/7038871 https://www.cnblogs.com/David3290/p/11378579.html