[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