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

convert strings into row

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