Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Posts
    3

    Unanswered: pl/sql: Splitting a string into an array?

    I can't find a specific category for pl/sql questions, so I'll ask here since it seems to best fit in Oracle.

    We've got a fair number of pages used for data entry served as web pages generated by pl/sql scripts. These have lots of text boxes that users make use of in a "spreadsheet" format and then submit their changes back to the server. This works, but is clunky and hard to navigate.

    To simplify things, I've modified some existing javascript and dhtml to make a javascript-based spreadsheet that looks and feels more like excel and other similar apps our suers regularly interact with. My problem, though, is getting the data bask into the pl/sql scripts with a minimum of rewriting. They're poorly documented, quite numerous, and I'd rather not do more to them than I must.

    The easiest way for me to pass this stuff back was to fill one hidden text box with a delimited string containing the return values. So, I'm passing back something like this:

    Code:
    55224;7120.0;7240.0;7360.0;7240.0;7500.0;7240.0
    I need to be able to work with that like an array back inside the script. The people behind pl/sql, however, felt it wise not to include a split function, or just about anything I can find that would make doing this easy.

    So I come to you for guidance. Can I split this back into an array, or would I be better off trying to have the javascript read and write to a ton of text boxes (all of which have the same name)?

    Any help is greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    You'll need to write a pl/sql loop, which parses out the next delimiter (";") using the instr function. Substr based on the instr loc, and return the pieces as needed. Keep in mind, when instr doesn't find a delim, it returns 0, which is bad for your substring. Also, read the help on instr, because it takes 4 params, and you'll want them.

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    I was bored, so I decided to write it for you.

    PHP Code:
    CREATE OR REPLACE PACKAGE STRING_FNC
    IS

    TYPE t_array IS TABLE OF VARCHAR2
    (50)
       
    INDEX BY BINARY_INTEGER;

    FUNCTION 
    SPLIT (p_in_string VARCHAR2p_delim VARCHAR2) RETURN t_array;

    END;

    CREATE OR REPLACE PACKAGE BODY STRING_FNC
    IS

       
    FUNCTION SPLIT (p_in_string VARCHAR2p_delim VARCHAR2) RETURN t_array 
       IS
       
          i       number 
    :=0;
          
    pos     number :=0;
          
    lv_str  varchar2(50) := p_in_string;
          
       
    strings t_array;
       
       
    BEGIN
       
          
    -- determine first chuck of string  
          pos 
    := instr(lv_str,p_delim,1,1);
       
          -- while 
    there are chunks leftloop 
          
    WHILE ( pos != 0LOOP
             
             
    -- increment counter 
             i 
    := 1;
             
             -- 
    create array element for chuck of string 
             strings
    (i) := substr(lv_str,1,pos);
             
             -- 
    remove chunk from string 
             lv_str 
    := substr(lv_str,pos+1,length(lv_str));
             
             -- 
    determine next chunk 
             pos 
    := instr(lv_str,p_delim,1,1);
             
             -- 
    no last chunkadd to array 
             IF 
    pos 0 THEN
            
                strings
    (i+1) := lv_str;
             
             
    END IF;
          
          
    END LOOP;
       
          -- return array 
          RETURN 
    strings;
          
       
    END SPLIT;

    END
    example how to use it

    Code:
    SQL> set serveroutput on
    SQL> declare
      2  
      3  str string_fnc.t_array;
      4  
      5  begin
      6  
      7     str := string_fnc.split('55224;7120.0;7240.0;7360.0;7240.0;7500.0;7240.0',';');
      8     
      9     for i in 1..str.count loop
     10     
     11         dbms_output.put_line(str(i));
     12     
     13     end loop;
     14     
     15  end;
     16  /
    
    55224;
    7120.0;
    7240.0;
    7360.0;
    7240.0;
    7500.0;
    7240.0
    
    PL/SQL procedure successfully completed.

  4. #4
    Join Date
    May 2004
    Posts
    3
    Wow. You rock. Thanks!

  5. #5
    Join Date
    May 2004
    Posts
    3
    As a side note for others who use this code: If you want to drop the delimter, make sure you have a trailing one and change:

    Code:
    strings(i) := substr(lv_str,1,pos);
    to:

    Code:
    strings(i) := substr(lv_str,1,pos-1);
    Thanks again.

  6. #6
    Join Date
    Jun 2004
    Posts
    1

    using a split array function directly with some PL/SQL

    I was looking for something similar and found this article. Not 100% what I was after as I wanted to use the results of split array in a SELECT statement, so I knocked up a stored procedure.

    The function below lets you do things like:

    Code:
    SELECT * FROM TABLE ( cast(utils_pkg.splitString('foo,bar,rar',',') AS T_SPLIT_TABLE))
    You need to create some types:

    Code:
    CREATE OR REPLACE TYPE T_SPLIT_ROW AS 
      OBJECT (ITEM VARCHAR2(255) );
    /
    CREATE OR REPLACE TYPE T_SPLIT_TABLE AS 
      TABLE OF T_SPLIT_ROW;
    /
    Then, compile a package like this one:

    Code:
    CREATE OR REPLACE PACKAGE Utils_Pkg AS
    
    /**
     * Split string into a table
     *
     * @param	in_string				String	 
     * @param	in_delimiter	Delimiter
     *
     */
    FUNCTION SplitString(
    	in_string				IN VARCHAR2,
    	in_delimiter			IN VARCHAR2
    ) RETURN T_SPLIT_TABLE;
    
    END Utils_Pkg;
    /
    Code:
    CREATE OR REPLACE PACKAGE BODY Utils_Pkg AS
    			
    /**
     * Split string into a table
     * example use:
     * SELECT * FROM TABLE ( cast(utils_pkg.splitString('foo,bar,rar',',') AS T_SPLIT_TABLE))
     *
     * @param	in_string		String	  
     * @param	in_delimiter	Delimiter
     *
     */
    FUNCTION SplitString(
    	in_string				IN VARCHAR2,
    	in_delimiter			IN VARCHAR2
    ) RETURN T_SPLIT_TABLE
    AS 
    	v_table 	T_SPLIT_TABLE := T_SPLIT_TABLE();
    	v_start 	NUMBER :=1;
    	v_pos	 	NUMBER :=0;
    BEGIN
    			 
    	-- determine first chuck of string 
    	v_pos := INSTR(in_string, in_delimiter, v_start);
    	
    	-- while there are chunks left, loop 
    	WHILE ( v_pos != 0) LOOP
    		-- create array
    		v_table.extend;
    		v_table( v_table.COUNT ) := T_SPLIT_ROW( SUBSTR(in_string, v_start, v_pos-v_start) );
    		v_start := v_pos + 1; 
    		v_pos := INSTR(in_string, in_delimiter, v_start);	
    	END LOOP;
    	-- add in last item
    	v_table.extend;
    	v_table( v_table.COUNT ) := T_SPLIT_ROW( SUBSTR(in_string, v_start) );
    
    	RETURN v_table;
    END;
    
    END Utils_Pkg;
    /

  7. #7
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    I use similar functions to split strings.

    One thing I like to do is to avoid the special case for the last portion of the string.

    So, I do something like this:

    my_string := RTRIM( string, delimiter ) || delimiter;
    This way, we can be sure that every string is terminated by the delimiter.

    Then, there is no need for the special case of the last element of the array.

    I also have a function that takes an array and converts it to a string. It handles arrays where there are gaps in the indexes. That is, if we have an array such as:

    my_array(1) := 'a';
    my_array(5) := 'x';
    my_array(12) :='i';
    Then, assuming that ';' is the delimiter, this get concatenated to
    a;i;x
    Ravi

  8. #8
    Join Date
    Apr 2011
    Posts
    1

    string into array

    You can try to run in loop this select and assign every row to an array

    SELECT substr(main_string, position_from + 1, position_to - position_from - 1)
    FROM (SELECT main_string,
    decode(rownum - 1, 0, 0, instr(main_string, ',', 1, rownum - 1)) position_from,
    instr(main_string, ',', 1, rownum) position_to
    FROM (SELECT '111,123,aaabbb,555,' main_string
    FROM dual)
    CONNECT BY LEVEL <= length(main_string))
    WHERE position_to > 0;

  9. #9
    Join Date
    Jun 2012
    Posts
    1
    Quote Originally Posted by allienb View Post
    You can try to run in loop this select and assign every row to an array

    SELECT substr(main_string, position_from + 1, position_to - position_from - 1)
    FROM (SELECT main_string,
    decode(rownum - 1, 0, 0, instr(main_string, ',', 1, rownum - 1)) position_from,
    instr(main_string, ',', 1, rownum) position_to
    FROM (SELECT '111,123,aaabbb,555,' main_string
    FROM dual)
    CONNECT BY LEVEL <= length(main_string))
    WHERE position_to > 0;
    Thank you very much this is very helpful and easy way to do the querying. You really make it so simple.

  10. #10
    Join Date
    Oct 2004
    Posts
    6
    cool way of doing it...does not work tho if the string has values after the final delimiter such as:
    '111,123,aaabbb,555,AAAA'

    this should work:

    SELECT substr(main_string, position_from + 1, decode(position_to, 0, length(main_string),position_to - position_from - 1))
    FROM (SELECT main_string,
    decode(rownum - 1, 0, 0, instr(main_string, ',', 1, rownum - 1)) position_from,
    instr(main_string, ',', 1, rownum) position_to
    FROM (SELECT '111,123,aaabbb,555,AAA' main_string
    FROM dual)
    CONNECT BY LEVEL <= length(main_string))
    WHERE position_to > 0 or position_from > 0



    Quote Originally Posted by allienb View Post
    You can try to run in loop this select and assign every row to an array

    SELECT substr(main_string, position_from + 1, position_to - position_from - 1)
    FROM (SELECT main_string,
    decode(rownum - 1, 0, 0, instr(main_string, ',', 1, rownum - 1)) position_from,
    instr(main_string, ',', 1, rownum) position_to
    FROM (SELECT '111,123,aaabbb,555,' main_string
    FROM dual)
    CONNECT BY LEVEL <= length(main_string))
    WHERE position_to > 0;

  11. #11
    Join Date
    Sep 2012
    Posts
    1
    Quote Originally Posted by ddjen11 View Post
    cool way of doing it...does not work tho if the string has values after the final delimiter such as:
    '111,123,aaabbb,555,AAAA'

    this should work:

    SELECT substr(main_string, position_from + 1, decode(position_to, 0, length(main_string),position_to - position_from - 1))
    FROM (SELECT main_string,
    decode(rownum - 1, 0, 0, instr(main_string, ',', 1, rownum - 1)) position_from,
    instr(main_string, ',', 1, rownum) position_to
    FROM (SELECT '111,123,aaabbb,555,AAA' main_string
    FROM dual)
    CONNECT BY LEVEL <= length(main_string))
    WHERE position_to > 0 or position_from > 0
    I have a similar requirement and was trying to follow your coding. I am not able to do it. Can you please help

    here is the example

    input_string
    case when "aa"."bb" || "aa"."cc" then "aa"."dd"
    else "aaa"."xx"
    end

    outputrows
    "aa"."bb"
    "aa"."cc"
    "aa"."dd"
    "aaa"."xx"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •