你好！

with t as (select "a:[{f:,h:,checindate:""month1：,year: ,day: "",checkoutdate:""month：,year: ,day: "",},

{checindate:""month2：,year: ,day: ,"",checkoutdate:""month：,year: ,day},

{checindate:""month3：,year: ,day: ,"",checkoutdate:""month：,year: ,day}]" str from dual)

,t1 as (SELECT substr(str,instr(str,"[")+1,instr(str,"]")-instr(str,"[")-1) str FROM T)

,t2 as (select substr(str,instr(str,"{")+1,instr(str,"}")-instr(str,"{")-1) str from t1)

select str,substr(str,instr(str,"checindate")+12, instr(str,"checkoutdate")-instr(str,"checindate")-12) from t2;

得到第一個checindate，直接擷取字串就可以了

別搞得那麼複雜了