无递归,需要辅助表
set @pattern_sub := '(from[ ]+[A-z0-9\.]+)|(join[ ]+[A-z0-9\.]+)|(drop[ ]+[A-z0-9\.]+)|(into[ ]+[A-z0-9\.]+)|(update[ ]+[A-z0-9\.]+)';
set @pattern_rep := '([fromFROM]+[ ]+)|([joinJOIN]+[ ]+)|([dropDROP]+[ ]+)|([intoINTO]+[ ]+)|([updateUPDATE]+[ ]+)';
set @pattern_4 := '(FROM[ ])|(JOIN[ ])|(DROP[ ])|(INTO[ ])';
set @pattern_6 := 'UPDATE[ ]';
set @cn_all := (
select
max((length(source) - length(regexp_replace(upper(source),@pattern_4,'')))/5
+
(length(source) - length(regexp_replace(upper(source),@pattern_6,'')))/7) as cn_all
from test.sqldic a
);
select source,table_name
from (
select a1.id as p_id ,a1.source,a2.id as f_id,
regexp_replace(regexp_substr(a1.source,@pattern_sub,1,a2.id,'i'),@pattern_rep,'') as table_name,
'辅助列' as d
from test.sqldic a1 join
(select id from test.assist a2 where id <=@cn_all) a2
) t
where table_name is not null