Oracle根据符号分隔字段内字符串后SQL查询数据方法

-- 从WITH函数子查询中获取需要的数据
SELECT COUNT(*) FROM CT_BC_BUSINESSCARDFLOW t1 WHERE t1.CFSerialNumber NOT IN (
-- 利用WITH函数实现子查询（当做内联视图或者临时表使用）
WITH t(sub, str) AS (
    SELECT substr(CFBUSINESSCARDFLOW, 1, instr(CFBUSINESSCARDFLOW, ';') - 1), substr(concat(CFBUSINESSCARDFLOW,';'), instr(CFBUSINESSCARDFLOW, ';') + 1) 
    FROM T_BC_BIZACCOUNTBILLENTRY ENTRY
    LEFT JOIN T_BC_BizAccountBill BILL ON ENTRY.FBILLID = BILL.FID
    -- 单据分录商务卡流水信息不为空
    WHERE ENTRY.CFBusinessCardFlow IS NOT NULL
    -- 单据状态(不是暂存/废弃/取消)
    AND BILL.FSTATE <> 20
    AND BILL.FSTATE <> 27
    AND BILL.FSTATE <> 50
    
	-- 字段内根据特定符号分隔数据
    UNION ALL
    
    SELECT substr(str, 1, instr(str, ';') - 1), substr(str, instr(str, ';') + 1)
    FROM t WHERE instr(str, ';') > 0
) 
-- 从临时表t中查询数据
SELECT sub
FROM t
WHERE sub is not null
)