Some Good SQL

Query to convert comma separated values into rows 

 with test as (select '1,2,3' col1 from dual) select regexp_substr(col1, '[^,]+', 1, rownum) result1 from test connect by level <= length(regexp_replace(col1, '[^,]+')) + 1 ;

WITH test AS (SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL UNION SELECT 'f,g' temp, 2 slno FROM DUAL UNION SELECT 'h' temp, 3 slno FROM DUAL) SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)), slno FROM test CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+') AND PRIOR slno = slno AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL ;
select REGEXP_SUBSTR('A,B,C,D,E','[^,]',1,level) from dual connect by level <= (select regexp_count('A,B,C,D,E',',') from dual);

SELECT EXTRACTVALUE(xt.column_value,'list') AS listitem FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('' || REPLACE('2,1',',','') || '') ,'/lists/list'))) xt ;


SELECT EXTRACT(VALUE(d), '//row/text()').getstringval() AS DATA FROM (SELECT XMLTYPE('' || REPLACE('1,2', ',', '') || '') AS xmlval FROM DUAL) x, TABLE(XMLSEQUENCE(EXTRACT(x.xmlval, '/rows/row'))) d;

Query to split number and character into two different columns 

with src as (select 'CGJ0000617' col_1 from dual
union all select 'CG0,001442' from dual
union all select 'CGJ0001444' from dual
union all select 'CMOV000GH0200' from dual
union all select 'CXAR00000001' from dual
union all select 'CXAR00000002' from dual)
 select regexp_substr(col_1,'^[A-Za-z]+') part_1 ,regexp_substr(col_1,'[0-9]+$') part_2 from src ;

Second highest salary SQL

select max(sal) from employees where sal not in (select max(sal) from employees) ;
select max(sal) from employees where sal < (select max(sal) from employees ) ;
select sal from (select sal,rownum rn from (select sal from employees order by sal desc)) where rn=2 ;
select sal from (select sal,row_number() over(order by sal desc) rn from employees) where rn=2 ;