12 Replies Latest reply: Dec 11, 2012 3:00 AM by BluShadow RSS

    Need a function to display each word of a string into individual row

    799706
      Guys,

      I have a requirement where I need to display each word of my string separated by comma into rows from DUAL statement. See the example below.

      SELECT dummy_function('Vikram','Sehwag','Uday') from DUAL

      Output is expected as follows in three rows for each word separated by comma

      Vikram
      Sehwag
      Uday

      Do we have any function that can do the same as explained above?

      Looking forward to your replies. Thanks.

      -Sunil
        • 1. Re: Need a function to display each word of a string into individual row
          BluShadow
          Sunil wrote:
          Guys,

          I have a requirement where I need to display each word of my string separated by comma into rows from DUAL statement. See the example below.

          SELECT dummy_function('Vikram','Sehwag','Uday') from DUAL
          That's 3 strings, not one string.

          If it was one string of comma seperated values then something like...
          SQL> ed
          Wrote file afiedt.buf
          
            1  with t as (select 'Vikram,Sehwag,Uday' as txt from dual)
            2  --
            3  -- end of test data
            4  --
            5  select regexp_substr(txt,'[^,]+',1,level) as word
            6  from   t
            7* connect by regexp_substr(txt,'[^,]+',1,level) is not null
          SQL> /
          
          WORD
          ------------------
          Vikram
          Sehwag
          Uday
          would do it.

          Please explain clearly what you are wanting to achieve.
          • 2. Re: Need a function to display each word of a string into individual row
            chris227
            Depends on your cleintt but somthing like this might work
            with data as (
            SELECT q'['Vikram','Sehwag','Uday']' s from DUAL
            )
            
            select
             replace(s,',',chr(10)) s
            from
            data
            
            'Vikram'
            'Sehwag'
            'Uday'
            • 3. Re: Need a function to display each word of a string into individual row
              BluShadow
              chris227 wrote:
              Depends on your cleintt but somthing like this might work
              with data as (
              SELECT q'['Vikram','Sehwag','Uday']' s from DUAL
              )
              
              select
              replace(s,',',chr(10)) s
              from
              data
              
              'Vikram'
              'Sehwag'
              'Uday'
              That's not 3 individual rows as the OP requested, that's a single row, displayed across 3 lines using newline characters.
              • 4. Re: Need a function to display each word of a string into individual row
                6363
                If it is three strings you can use the supplied data type ODCIVarchar2List

                http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/ext_types_ref.htm#ADDCI5016

                http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/introduction.htm#ADDCI4250
                SQL> select column_value from
                  2  table(sys.odcivarchar2list('Vikram','Sehwag','Uday'));
                
                COLUMN_VALUE
                ------------------------------------------------------------
                Vikram
                Sehwag
                Uday
                • 5. Re: Need a function to display each word of a string into individual row
                  BluShadow
                  If it was three seperate strings I'd just do...
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  select 'Vikram' as word from dual union all
                    2  select 'Sehwag' from dual union all
                    3* select 'Uday' from dual
                  SQL> /
                  
                  WORD
                  ------
                  Vikram
                  Sehwag
                  Uday
                  But going on the subject line "display each word of _a_ string" suggests the OP has just one string and wants to extract words from it.

                  I guess we'll never know unless the OP bothers to reply.
                  • 6. Re: Need a function to display each word of a string into individual row
                    ranit B
                    Blu,
                    Your code works if table has exactly 1 row.

                    With this data... see what it gives... *14 rows* -
                    with t as (
                        select 'Vikram,Sehwag,Uday' as txt from dual 
                        union all
                        select 'a,b,c' as txt from dual
                    )
                    select regexp_substr(txt,'[^,]+',1,level) as word
                        from   t
                    connect by regexp_substr(txt,'[^,]+',1,level) is not null;
                    Please put a DISTINCT in SELECT. B-)
                    select DISTINCT regexp_substr(txt,'[^,]+',1,level) as word
                        from   t
                    connect by regexp_substr(txt,'[^,]+',1,level) is not null;
                    Edited by: ranit B on Dec 10, 2012 7:55 PM
                    • 7. Re: Need a function to display each word of a string into individual row
                      BluShadow
                      ranit B wrote:
                      Blu,
                      Your code works if table has exactly 1 row.

                      With this data... see what it gives... *14 rows* -
                      "Hey, look, if I change the requirement to something that is completely different from what was asked for then your code doesn't work"

                      Genius!

                      Yeah, great.... but the OP hasn't specified it needs to deal with multiple rows. The OP specified "each word of _*A*_ (singular) string" and "each word of my string" (singular string). No mention of multiple rows of data.

                      If the OP wants a solution to work with multiple strings or rows, then they can ask for it.

                      So don't be an idiot and pull apart a solution by saying it doesn't work if you change the requirements.
                      • 8. Re: Need a function to display each word of a string into individual row
                        BluShadow
                        ranit B wrote:
                        Blu,
                        Your code works if table has exactly 1 row.

                        With this data... see what it gives... *14 rows* -
                        with t as (
                        select 'Vikram,Sehwag,Uday' as txt from dual 
                        union all
                        select 'a,b,c' as txt from dual
                        )
                        select regexp_substr(txt,'[^,]+',1,level) as word
                        from   t
                        connect by regexp_substr(txt,'[^,]+',1,level) is not null;
                        Please put a DISTINCT in SELECT. B-)
                        select DISTINCT regexp_substr(txt,'[^,]+',1,level) as word
                        from   t
                        connect by regexp_substr(txt,'[^,]+',1,level) is not null;
                        And your solution is wrong anyway... you wouldn't want to generate the rows and then distinct them... you'd want to restrict them as part of the hierarchical query...
                        SQL> ed
                        Wrote file afiedt.buf
                        
                          1  with t as (select 'Vikram,Sehwag,Uday' as txt from dual union all
                          2             select 'Fred,Bob,Jim,Sam' from dual)
                          3  --
                          4  -- end of test data
                          5  --
                          6  select regexp_substr(txt,'[^,]+',1,level) as word
                          7  from   t
                          8  connect by regexp_substr(txt,'[^,]+',1,level) is not null
                          9  and txt = prior txt
                         10* and prior sys_guid() is not null
                        SQL> /
                        
                        WORD
                        ------------------
                        Fred
                        Bob
                        Jim
                        Sam
                        Vikram
                        Sehwag
                        Uday
                        
                        7 rows selected.
                        • 9. Re: Need a function to display each word of a string into individual row
                          6363
                          BluShadow wrote:
                          If it was three seperate strings I'd just do...
                          SQL> ed
                          Wrote file afiedt.buf
                          
                          1  select 'Vikram' as word from dual union all
                          2  select 'Sehwag' from dual union all
                          3* select 'Uday' from dual
                          SQL> /
                          
                          WORD
                          ------
                          Vikram
                          Sehwag
                          Uday
                          I don't know about just, it seems more complicated and less useful for a varying set of values.
                          But going on the subject line "display each word of _a_ string" suggests the OP has just one string and wants to extract words from it.
                          And since the original post showed an example that contradicted the subject and had multiple strings then at least some of the question was wrong so I thought I would provide an example that answered the question as stated rather than the subject.
                          I guess we'll never know unless the OP bothers to reply.
                          Agreed and it doesn't hurt to have answers to both variations of the question posted, it isn't like there are a multitude of interpretations.
                          • 10. Re: Need a function to display each word of a string into individual row
                            BluShadow
                            3360 wrote:
                            I don't know about just, it seems more complicated and less useful for a varying set of values.
                            LOL, bit of an assumption with the "varying". All we've been given is 3 fixed strings, so it's neither here not there... my point was simply that if they are fixed strings, then may as well just union them to give seperate rows... no need for any fancy bells and whistles. If they was a varying number of strings, then that would require dynamic code to get it into a function or as unions, but re-read the original question, it's asking for "each word of a string" implying there is only one string and the words are in that one string seperated by commas.
                            But going on the subject line "display each word of _a_ string" suggests the OP has just one string and wants to extract words from it.
                            And since the original post showed an example that contradicted the subject and had multiple strings then at least some of the question was wrong so I thought I would provide an example that answered the question as stated rather than the subject.
                            Yeah, but the subject line and the commentry described a single string, but the code that the OP didn't know how to get working was the bit that had 3 seperate strings, so I'm guessing the wording is the more accurate description of the issue. ;)

                            I guess we'll never know unless the OP bothers to reply.
                            Agreed and it doesn't hurt to have answers to both variations of the question posted, it isn't like there are a multitude of interpretations.
                            True.
                            • 11. Re: Need a function to display each word of a string into individual row
                              6363
                              BluShadow wrote:
                              3360 wrote:
                              I don't know about just, it seems more complicated and less useful for a varying set of values.
                              LOL, bit of an assumption with the "varying". All we've been given is 3 fixed strings, so it's neither here not there... my point was simply that if they are fixed strings, then may as well just union them to give seperate rows... no need for any fancy bells and whistles.
                              I don't think it is a bit of an assumption. When someone shows a sample or example in a question, it usually is just that and that actual data will vary. I would find it highly unusual if we took all sample data posted here as literally representing the entire actual data set.
                              If they was a varying number of strings, then that would require dynamic code to get it into a function or as unions,
                              No it wouldn't it would depend on where it was input and coming from.
                              but re-read the original question, it's asking for "each word of a string" implying there is only one string and the words are in that one string seperated by commas.
                              I don't need to re-read it. I saw from the beginning that the subject and the body of the question say two separate things. Which is why I prefaced the statement with If when I said
                              If it is three strings you can use the supplied data type ODCIVarchar2List
                              Just because I answered the body of the question which clearly showed multiple strings does not mean I have trouble reading.
                              • 12. Re: Need a function to display each word of a string into individual row
                                BluShadow
                                3360 wrote:
                                Just because I answered the body of the question which clearly showed multiple strings does not mean I have trouble reading.
                                I wasn't suggesting that. It's a friendly discussion about what the OP was intending... no intention of malice... I was really just stating my own observations. ;)

                                I'm now guessing that as he's marked my first answer as helpful, that it really was a single string with comma seperated data in it he was after. Perhaps he's too shy to reply. :-/