Converting a string into Oracle column

In most of the time we come across for splitting the string and to display in one column for further validation or on our business/ project requirement , for example lets say we have a string like ‘1,2,3’ or ‘A,B,C,D’ or ‘1,A,2,B’ and we want to display like the below:

OUTPUT:
———-
1
2
3

OR

OUTPUT:
———-
A
B
C
D

OR

OUTPUT:
———-
1
A
2
B

This can be achieved by using various ways using PLSQL.

Below a simple PLSQL block where you can run and check few ways to achieve as per the required OUTPUT:


DECLARE

        L_INP_VAR VARCHAR2(200) := '1,2,3';

        l_out VARCHAR2(100);

BEGIN
        /*******My input******/
        dbms_output.put_line('My input is = ' || L_INP_VAR);

        /*Process 1 */
        SELECT L_INP_VAR INTO l_out FROM dual;
        dbms_output.put_line('***** My Simple variable pass will display as below without any change: ****');
        dbms_output.put_line(l_out);
        /*Process 2 */

        dbms_output.put_line('**  Using XMLTYPE we will get the result as below **');

        FOR i IN (SELECT extract(VALUE(d)
                                ,'//row/text()') .getstringval() AS RESULT
                    FROM (SELECT xmltype('<rows><row>' ||
                                         REPLACE(L_INP_VAR
                                                ,','
                                                ,'</row><row>') || '</row></rows>') AS xmlval
                            FROM dual) x
                        ,TABLE(xmlsequence(extract(x.xmlval
                                                  ,'/rows/row'))) d)
        LOOP
                dbms_output.put_line(i.RESULT);
        END LOOP;

        /*Process 3 */

        dbms_output.put_line('**  Using REGEXP expression we will get the result as below **');

        FOR j IN (SELECT regexp_substr(L_INP_VAR
                                      ,'[^,]+'
                                      ,1
                                      ,LEVEL) myinput
                    FROM dual
                  CONNECT BY LEVEL <= regexp_count(L_INP_VAR
                                                  ,',') + 1)
        LOOP
                dbms_output.put_line(j.myinput);
        
        END LOOP;
        /*Process 4 */
        dbms_output.put_line('If the string is only Alphabets then we can make use of dbms_utility.comma_to_table .');
        dbms_output.put_line('By using dbms_utility.comma_to_table the result will be someting like as below : ');
        DECLARE
                p_list VARCHAR2(4000) := 'A,B,C';
                --p_list   VARCHAR2(4000) := '1,2,3';
                r_lname  dbms_utility.lname_array;
                v_length BINARY_INTEGER;
        
        BEGIN
        
                dbms_utility.comma_to_table(p_list
                                           ,v_length
                                           ,r_lname);
        
                FOR j IN r_lname.FIRST .. r_lname.LAST
                LOOP
                        dbms_output.put_line(r_lname(j));
                
                END LOOP;
        
        END;
        /*Process 5*/
        dbms_output.put_line('If the string is only Alphabets then we can also make use of DBMS_UTILITY.UNCL_ARRAY .');
        dbms_output.put_line('By using DBMS_UTILITY.UNCL_ARRAY the result will be someting like as below : ');

        DECLARE
                v_initialstring VARCHAR2(100) := 'A,B,C,D';
                v_table         dbms_utility.uncl_array;
                v_finalstring   VARCHAR2(100);
                v_tablen        BINARY_INTEGER;
        BEGIN
                dbms_output.put_line('Initial string: ' || v_initialstring);
                dbms_utility.comma_to_table(v_initialstring
                                           ,v_tablen
                                           ,v_table);
                dbms_output.put_line('Table (length = ' || v_tablen || '):');
        
                FOR v_count IN 1 .. v_table.COUNT
                LOOP
                        dbms_output.put_line(v_table(v_count));
                END LOOP;
        END;
        /*Process 6*/
        dbms_output.put_line('By using dbms_sql.varchar2_table with SUBSTR function we can get any string converted to table below : ');
        DECLARE
                v_string   VARCHAR2(32767) := '1,2,3'; --input 
                v_nfields  PLS_INTEGER := 1;
                v_table    dbms_sql.varchar2_table;
                v_delimpos PLS_INTEGER := instr(v_string
                                               ,',');
                v_delimlen PLS_INTEGER := length(',');
        BEGIN
        
                WHILE v_delimpos > 0
                LOOP
                        v_table(v_nfields) := substr(v_string
                                                    ,1
                                                    ,v_delimpos - 1);
                        v_string := substr(v_string
                                          ,v_delimpos + v_delimlen);
                        v_nfields := v_nfields + 1;
                        v_delimpos := instr(v_string
                                           ,',');
                END LOOP;
                v_table(v_nfields) := v_string;
        
                FOR k IN v_table.FIRST .. v_table.LAST
                LOOP
                
                        dbms_output.put_line(v_table(k));
                END LOOP;
        END;

END;
 
Advertisements

6 thoughts on “Converting a string into Oracle column

  1. I really appreciate this post. I’ve been looking all over for this! Thank goodness I found it on Bing. You’ve made my day! Thanks again! ddbagfeeddac

  2. Oh my goodness! Incredible article dude! Thank you,
    However I am encountering problems with your
    RSS. I don’t know why I am unable to join it. Is there anyone else having similar RSS problems?
    Anyone that knows the solution will you kindly respond?
    Thanx!!

  3. I do consider all of the concepts you’ve introduced on your post.
    They’re really convincing and will definitely
    work. Nonetheless, the posts are too short for newbies.
    May you please lengthen them a little from subsequent time?

    Thank you for the post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s