This discussion is archived
12 Replies Latest reply: Dec 11, 2012 1:00 AM by BluShadow RSS

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

799706 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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. :-/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points