Oracle Advanced SQL Statements, by example

Index
1. Retrieve only numbers from a mixed varchar+numbers text.

1. Retrieve only numbers from a mixed varchar+numbers text.
2. Replace comma delimited list to a column.
3. Replace column to comma delimited list

SQL> SELECT max_segment_blocks, 
     TO_NUMBER(REGEXP_REPLACE (max_segment_blocks, '[^0-9]+','')) max_segment_blocks_num
       FROM  SEGMENT_USAGE_HIST;

MAX_SEGMENT_BLOCKS             MAX_SEGMENT_BLOCKS_NUM
------------------------------ ----------------------
     200,352 blocks                            200352
     424,960 blocks                            424960
       4,992 blocks                              4992
       1,152 blocks                              1152
   1,292,632 blocks                           1292632
          96 blocks                                96
     401,408 blocks                            401408
     266,240 blocks                            266240
   2,842,624 blocks                           2842624
   1,794,048 blocks                           1794048


2. Replace comma delimited list to a column.

SQL> SELECT REGEXP_SUBSTR(txt, '[^,]+', 1, level)
  2   FROM (SELECT '667,668' AS txt FROM DUAL)
  3  CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(txt, '[^,]')) + 1;

REGEXP_SUBSTR(TXT,'[^,]+',1,LE
------------------------------
667
668

Intermediate step :

SQL> SELECT *
  2   FROM (SELECT '667,668' AS txt FROM DUAL)
  3  CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(txt, '[^,]')) + 1;

TXT
-------
667,668
667,668 

3. Replace column to comma delimited list

SELECT g.owner, 
       g.log_group_name, 
       g.table_name, 
       g.log_group_type,
       LISTAGG(c.column_name, ', ') WITHIN GROUP 
              (ORDER BY c.POSITION) column_list
  FROM dba_log_groups g,
       dba_log_group_columns c
 WHERE g.owner = c.owner(+)
   AND g.log_group_name = c.log_group_name(+)
   AND g.table_name = c.table_name(+)
 GROUP BY g.owner, g.log_group_name, g.table_name, g.log_group_type
 ORDER BY g.owner, g.log_group_name, g.table_name, g.log_group_type;

OWNER  LOG_GROUP_NAME  TABLE_NAME  LOG_GROUP_TYPE  COLUMN_LIST
------ --------------- ----------- --------------- --------------
SYS    ENC$_LOG_GRP    ENC$        USER LOG GROUP  OBJ#, OWNER#

SYS    SEQ$_LOG_GRP    SEQ$        USER LOG GROUP  OBJ#

Same Syntax, get a list of columns in a table into a list
SELECT LOWER( 
       LISTAGG(column_name||',') WITHIN GROUP (ORDER BY column_id) 
            ) column_list
  FROM USER_TAB_COLUMNS 
 WHERE table_name = 'AVG_REPORTS_ROAMERS_DATA'

community_id,network_id,country_id,date_of_record,roamers,roamers_gap,total_sesn_rel_action_mintime,total_sesn_rel_action_manual,total_ses_rel_action_subinact,total_sesn_rel_act_subinactbw,total_sesn_rel_action_no_acces,sesn_rel_act_non_pref_allowed,total_sesn_rel_action_maxred,total_sesn_rel_action_maxrej,sesn_rel_1pn_priority_mode,total_sesn_rel_gsm_after_gprs,sesn_rel_prof_rej_nw_once,src_network_id,total_voice_usage,total_data_usage,total_sms_usage,total_voice_charge,total_data_charge,total_sms_charge,total_subscribers_only_voice,total_subscribers_only_data,total_subscribers_voice_data,total_subscribers_no_usage,total_voice_subscribers,total_data_subscribers,total_sms_subscribers,total_voice_mt_usage,total_voice_mt_charge,total_voice_mt_subscribers,total_subs_only_voice_mt,total_sesn_rel_act_gprs_relay,total_sesn_subsequentmixtecreg,total_sesn_rel_act_nw_in_pnl,target,nw_preferred_mode,nw_type_id,nw_activation_status,total_sesn_rel_gsm_ssv_logic,total_sesn_rel_gprs_ssv_logic,total_csfb,total_sesn_rel_action_no_data,total_sesn_rel_lte_after_ss7,