怎样提取数据库字符串中用逗号隔开的字串？该如何解决_Oracle面试

怎样提取数据库字符串中用逗号隔开的字串？
怎样提取数据库字符串中用逗号隔开的字串？
比如一个字符串 USA,England,China,Japan
要提取出字串 USA England China 和 Japan

------解决方案--------------------------------------------------------

SQL code

with tab as(select 'USA,England,China,Japan'  id from dual)select substr(','||id||',',instr(','||id||',',',',1,level)+1,          instr(','||id||',',',',1,level+1)-instr(','||id||',',',',1,level)-1) newidfrom tabconnect bylevel <= length(','||id||',') - length(replace(','||id||',', ',', ''))-1--10g的話，用正則表達式簡單點:with tab as(select 'USA,England,China,Japan'  id from dual)select regexp_substr(id,'[^,]+',1,level) as cl1from tabconnect bylevel<=length(id)-length(replace(id,',',''))+1
------解决方案--------------------------------------------------------
SQL codeSQL> select regexp_substr('USA,England,China,Japan','[^,]+',1,level)  2         as cols  3  from dual  4  connect by  5          level<=length('USA,England,China,Japan')-  6          length(replace('USA,England,China,Japan',',',''))+1;COLS----------------------------------------------USAEnglandChinaJapan