Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Unanswered: how to get alternative strings in list of strings.

    Hi experts,

    how to get alternative strings in list of strings.

    eg

    'country1, italy, country2, india, country3, paris, country4, scottland, country5, rome'

    from the above list i need to fetch all the countries and their names separately.

    sample output
    ==============
    country1
    country2
    country3
    country4
    country5


    italy
    india
    paris
    scottland
    rome

    i am trying the above one in the below fashion

    SELECT SUBSTR
    ('country1, italy, country2, india, country3, paris, country4, scottland, country5, rome',
    1,
    INSTR
    ('country1, italy, country2, india, country3, paris, country4, scottland, country5, rome',
    ',',
    1,
    1
    )
    - 1
    )
    FROM DUAL

    can you please any one help me

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    1) Paris and Rome are not countries.
    2) What is the purpose of your request?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    assuming you have 10g or above

    Code:
    with test as
    (select 'country1, italy, country2, india, country3, paris, country4, scottland, country5, rome' col from dual)
     select trim(regexp_substr(col, '[^,]+', 1, rownum)) result
    from test
    connect by level <= length(regexp_replace(col, '[^,]+')) + 1;
    Never mind, does not meet the requirement.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thoght that the issue might be easy to understand(and easy to solve) by aparting the issue in two steps
    That might be ...
    (1) Decompose the input string x into elements(i.e. countries and names),
    using like beilstwh's way.

    (2) Order the decomposed elements as you wanted in final output, like ...
    sample output
    ==============
    country1
    country2
    country3
    country4
    country5


    italy
    india
    paris
    scottland
    rome

    By the way,
    I couldn't understand the rational to order the output like the OP's way.
    I like the output format in other ways, for exampe like this ...
    Code:
    Country              Name                
    -------------------- --------------------
    country1             italy               
    country2             india               
    country3             paris               
    country4             scottland           
    country5             rome
    I thought the LKBrwn_DBA's second question
    2) What is the purpose of your request?
    might be came from similar doubt of me.
    Last edited by tonkuma; 09-06-13 at 19:30. Reason: Add mention to beilstwh's way.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I don't know much about Oracle.
    But, no response was seen over one day.
    So, I want to show my example.
    (it was not tesed on Oracle and tested briefly on DB2 9.7 for Windows only.)

    Though, you might be neccesary to make some ammendments for the example to conform to syntax of Oracle,
    it might serve as a reference to solve your issue.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_test_data
    ( list_of_strings ) AS (
    VALUES
    /* SELECT */
      'country1, italy, country2, india, country3, paris, country4, scottland, country5, rome'
    /*  FROM  dual */
    )
    /**
    *** (1) Decompose the input string x /*i.e. list_of_strings*/
     into elements(i.e. countries and names).
    ***/
    , decompose_elements
    ( n , rest_of_string , element1 /*country*/ , element2 /*name*/ ) AS (
    SELECT 0 , list_of_strings || ','
         , '' , ''
     FROM  sample_test_data
    UNION ALL
    SELECT n + 1
         , SUBSTR(rest_of_string , comma_2nd + 1)
         , LTRIM( SUBSTR(rest_of_string , 1             , comma_1st - 1) )
         , LTRIM( SUBSTR(rest_of_string , comma_1st + 1 , comma_2nd - comma_1st - 1) )
     FROM  (SELECT n
                 , rest_of_string
                 , INSTR(rest_of_string , ',' , 1 , 1) AS comma_1st
                 , INSTR(rest_of_string , ',' , 1 , 2) AS comma_2nd
             FROM  decompose_elements
             WHERE n < 1000
               AND rest_of_string > ''
           )
    )
    /**
    *** (2) Order the decomposed elements as you wanted in final output.
    ***/
    SELECT element AS sample_output
     FROM  (SELECT 1 AS k , n
                 , element1 AS element
             FROM  decompose_elements
             WHERE n > 0
            UNION ALL
            SELECT 2 AS k , n
                 , element2 AS element
             FROM  decompose_elements
           )
     ORDER BY
           k , n
    ;
    ------------------------------------------------------------------------------
    
    SAMPLE_OUTPUT                                                                          
    ---------------------------------------------------------------------------------------
    country1                                                                               
    country2                                                                               
    country3                                                                               
    country4                                                                               
    country5                                                                               
                                                                                           
    italy                                                                                  
    india                                                                                  
    paris                                                                                  
    scottland                                                                              
    rome                                                                                   
    
      11 record(s) selected.

Tags for this Thread

Posting Permissions

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