This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Dec 28, 2012 7:10 AM by AlbertoFaenza RSS

convert strings into row

Vi Explorer
Currently Being Moderated
Hi,

I need to convert a string into row with their sum of position . I need to achieve this using a SQL.
Example :

String -  SQLPL/SQL 

my output should be

char   sum_position
------  --------------------
S          8      --> Sum of 1st and 7th position
Q          10    --> Sum of 2nd and 8th position
L           17    --> Sum of 3rd, 5th and 9th position
P           4     --> 4th position
/           6     --> 6 th position
  • 1. Re: convert strings into row
    908002 Expert
    Currently Being Moderated
    select s, sum(r) from (select substr('SQLPL/SQL',rownum,1)s, rownum r from dual
    connect by 1=1 and rownum<= length('SQLPL/SQL'))
    group by s;
  • 2. Re: convert strings into row
    N_i_R_v_A_n_A Explorer
    Currently Being Moderated
     select srt,sum(r) 
     from  (select substr('SQLPL/SQL',level,1) srt,rownum r from dual
     connect by level <= (select length('SQLPL/SQL') from dual))
     group by srt
  • 3. Re: convert strings into row
    Vi Explorer
    Currently Being Moderated
    thank you so much.. if I have more than one row is it possible to get??
    Example :
     
     SQLPL/SQL  --> record 1
     SQL            --> record 2
     
    my output should be
     
    char   sum_position
    ------  --------------------
    S          9      --> Sum of 1st and 7th position from first record and 1st position from second record
    Q          12    --> Sum of 2nd and 8th position from first record and 2nd position from second record
    L           20    --> Sum of 3rd, 5th and 9th position from first record and 3rd position from second record
    P           4     --> 4th position
    /           6     --> 6 th position
    Thank you in advance for your help.
  • 4. Re: convert strings into row
    N_i_R_v_A_n_A Explorer
    Currently Being Moderated
    considering the values are in a column of a table,

    you could modify the query where clause as:


    connect by level <=LENGTH(col)
    and col = prior col
    and prior sys_guid() is not null

    THIS WOULD DO IT.

    tRY IT OUT,LET ME KNOW IF YOU GET STUCK!!
  • 5. Re: convert strings into row
    N_i_R_v_A_n_A Explorer
    Currently Being Moderated
    testing IS THE TABLE I CREATED WITH COLUMN NAME ename AND VALUES that YOU HAVE


    SELECT SRT , SUM(R) FROM( select substr(ENAME,level,1) srt,rownum r from TESTING
    connect by level <= REGEXP_COUNT(ENAME,'.')
    AND ENAME = prior ENAME
    and prior sys_guid() is not null
    )
    GROUP BY SRT
  • 6. Re: convert strings into row
    Manik Expert
    Currently Being Moderated
    Try this:
    WITH t AS
            (SELECT 'SQLPL/SQL' str FROM DUAL
             UNION ALL
             SELECT 'SQL' str FROM DUAL),
         t1 AS
            (    SELECT REGEXP_SUBSTR (str,
                                       '[^,]+',
                                       1,
                                       LEVEL)
                           str,
                        LEVEL r
                   FROM (SELECT REGEXP_REPLACE (str, '(.)', '\1 ,') str FROM t)
             CONNECT BY     REGEXP_SUBSTR (str,
                                           '[^,]+',
                                           1,
                                           LEVEL)
                               IS NOT NULL
                        AND PRIOR str = str
                        AND PRIOR SYS_GUID () IS NOT NULL)
      SELECT SUM (r), str
        FROM t1
    GROUP BY str;
    Cheers,
    Manik.
  • 7. Re: convert strings into row
    908002 Expert
    Currently Being Moderated
    with t as ( select 'SQLPL/SQL' str from dual
    union
    select 'SQL' from dual)
    select s, sum(r) from (select substr(t.str,level,1)s, level r from t
    connect by level <=LENGTH(t.str)
    and str= prior str
    and prior sys_guid() is not null)
    group by s
  • 8. Re: convert strings into row
    ranit B Expert
    Currently Being Moderated
    WITH xx AS (
        SELECT 'SQLPL/SQL' str FROM DUAL
    )
      SELECT s1, SUM (s2)
        FROM (
               SELECT SUBSTR (str, LEVEL, 1) s1, LEVEL s2
                  FROM xx
               CONNECT BY LEVEL <= LENGTH (str))
    GROUP BY s1;
    gives
    P     4
    Q     10
    /     6
    S     8
    L     17
  • 9. Re: convert strings into row
    Vi Explorer
    Currently Being Moderated
    Hi Manik,

    your query worked to me.. but As I have more than 50,000 record in my table, it's taking too long time. more than half hour its processing the query.
  • 10. Re: convert strings into row
    Vi Explorer
    Currently Being Moderated
    Hi Nirvana & Kiran,

    The given query returning the wrong sum of position count . even though it's a same character but as it is in different rows we need to consider the position type as per the row. i.e. starting from 1 st position.
  • 11. Re: convert strings into row
    Vi Explorer
    Currently Being Moderated
    Hi Kiran,

    Sorry. The given query is working correctly. but however its taking too much time to execute. I have approximately 60,000 records in my table.
  • 12. Re: convert strings into row
    Vi Explorer
    Currently Being Moderated
    any help on this????
  • 13. Re: convert strings into row
    Solomon Yakobson Guru
    Currently Being Moderated
    Vi wrote:
    but however its taking too much time to execute. I have approximately 60,000 records in my table.
    Try:
    select  substr(your_column,position,1) "char",
            sum(position) sum_position
      from  your_table e,
            (
             select  level position
               from  dual
               connect by level <= (select max(length(your_column)) from your_table)
            )
      where position <= length(your_column)
      group by e.rowid,
               substr(your_column,position,1)
    /
    SY.
  • 14. Re: convert strings into row
    N_i_R_v_A_n_A Explorer
    Currently Being Moderated
    SELECT SRT , SUM(R) FROM( select substr(ENAME,level,1) srt,level r from TESTING
    connect by level <= REGEXP_COUNT(ENAME,'.')
    AND ENAME = prior ENAME
    and prior sys_guid() is not null
    )
    GROUP BY SRT
1 2 Previous Next

Legend

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