Home » SQL & PL/SQL » SQL & PL/SQL » selecting by adding a value to comma separated record
selecting by adding a value to comma separated record [message #376922] Fri, 19 December 2008 03:06 Go to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
hi
i've the following table with the following data:
SQL> select * from temp2;
 
NM
------------
1
2,3
 


Now i want to see the data in temp2 table as 1+10=11 ... 2+10=12 ... 3+10=13
means i want to retrieve the data by adding value 10 to each value in temp2 table

how is that possible using a query

Please let me know if i'm not clear
Re: selecting by adding a value to comma separated record [message #376926 is a reply to message #376922] Fri, 19 December 2008 03:27 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

2,3 in a single Row ???...

if not

select NM+10 OP from  temp2.


Re: selecting by adding a value to comma separated record [message #376965 is a reply to message #376926] Fri, 19 December 2008 04:45 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
2,3 is in single row

SQL> with split
  2        as (
  3         select no, rno, list,to_number(list)+10 new_list
  4          from (select no, rno, regexp_substr(nm,'[^,]+', 1, rno) list
  5             from (select rownum no, nm
  6                    from temp2) temp2, (select rownum rno
  7                        from (select max(NVL( LENGTH( REGEXP_REPLACE( nm, '[^,]+', NULL ) ), 0 ))
 len
  8                                from temp2)
  9                     connect by level <= len+1) l
 10            where regexp_substr(nm,'[^,]+', 1, rno) is not null)
 11          ),
 12     stragg
 13       as (
 14        select ltrim(sys_connect_by_path(new_list,','),',') list
 15           from split
 16          where connect_by_isleaf = 1
 17          start with rno = 1
 18        connect by rno = prior rno +1
 19                    and no = prior no
 20          )
 21     select * from stragg
 22  /

LIST
-----------
11
12,13



The above code is working fine ...
Re: selecting by adding a value to comma separated record [message #377005 is a reply to message #376965] Fri, 19 December 2008 07:12 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select '1' val from dual union all select '2,3' from dual)
  2  select regexp_replace(val,'([url=/wiki/:digit:]:digit:[/url])','1\1') from data;
REGEXP_REPLACE(VAL,'([url=/wiki/:DIGIT:]:DIGIT:[/url])','1\1')
-----------------------------------------
11
12,13

2 rows selected.

Regards
Michel
Previous Topic: Trigger help required
Next Topic: Pass partition name as parameter from procedure to the cusrsor in pl/sql
Goto Forum:
  


Current Time: Fri Dec 09 09:47:18 CST 2016

Total time taken to generate the page: 0.10325 seconds