1 2 Previous Next 19 Replies Latest reply: Dec 28, 2012 9:10 AM by AlbertoFaenza RSS

    convert strings into row

    VI
      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
          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
             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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                any help on this????
                                • 13. Re: convert strings into row
                                  Solomon Yakobson
                                  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
                                    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