Home » SQL & PL/SQL » SQL & PL/SQL » String sorting (Oracle 10g)
String sorting [message #338265] |
Mon, 04 August 2008 06:29  |
himang
Messages: 282 Registered: March 2005 Location: Bangalore
|
Senior Member |

|
|
I have a requirement to sort a comma seperated string. For example if I pass '1234,432,123,45322,56786' as string, then it should return '123,432,1234,45322,56786', after sorting the numbers inside the string.
I have done it creating Global Temporary table. Is there a way without creating the Temp table. I understand I can write the whole logic to sort and append the string, but if there is any direct way.
CREATE GLOBAL TEMPORARY TABLE TEMP_TAB(COL1 VARCHAR2(100)) ON COMMIT DELETE ROWS;
CREATE OR REPLACE FUNCTION func_sort_string(pi_string IN VARCHAR2, pi_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_str VARCHAR2(2000) DEFAULT pi_string || ',';
l_str2 VARCHAR2(2000);
l_n PLS_INTEGER := 0;
BEGIN
LOOP
l_n := INSTR(l_str, ',');
EXIT WHEN (NVL(l_n,0) = 0);
INSERT INTO TEMP_TAB(COL1) VALUES (TRIM(SUBSTR(l_str,1,l_n -1)));
l_str := SUBSTR( l_str, l_n +1 );
END LOOP;
FOR rec_sel IN (SELECT COL1 FROM TEMP_TAB ORDER BY TO_NUMBER(COL1)) ---cur_sel
LOOP
l_str2 := l_str2 || ',' || rec_sel.col1;
END LOOP;
COMMIT;
RETURN SUBSTR(l_str2,2);
END func_sort_string;
/
SQL> select func_sort_string('1234,432,123,45322,56786') from dual;
FUNC_SORT_STRING('1234,432,123,45322,56786')
--------------------------------------------------------------------------------
123,432,1234,45322,56786
|
|
|
Re: String sorting [message #338273 is a reply to message #338265] |
Mon, 04 August 2008 07:00   |
Dipali Vithalani
Messages: 278 Registered: March 2007 Location: India
|
Senior Member |
|
|
There is no direct way to sort multiple numbers stored within one string and then again to store those sorted number in one string...(as per your requirement). You must have to write a pl/sql code for it (what you have already done).
Regards,
Dipali..
|
|
|
Re: String sorting [message #338278 is a reply to message #338265] |
Mon, 04 August 2008 07:44   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> with
2 data as (select ','||'1234,432,123,45322,56786'||',' val from dual),
3 lines as (
4 select level line
5 from data
6 connect by level < length(val)-length(replace(val,',',''))
7 ),
8 all_values as (
9 select to_number(substr(val,
10 instr(val,',',1,line)+1,
11 instr(val,',',1,line+1)-instr(val,',',1,line)-1))
12 val
13 from data, lines
14 ),
15 ordered_values as (
16 select val,
17 row_number() over (order by val) rn,
18 count(*) over () cnt
19 from all_values
20 )
21 select substr(sys_connect_by_path(val,','),2) value
22 from ordered_values
23 where rn = cnt
24 connect by prior rn = rn - 1
25 start with rn = 1
26 /
VALUE
-------------------------------------------------------------
123,432,1234,45322,56786
1 row selected.
Regards
Michel
|
|
|
|
|
Re: String sorting [message #338395 is a reply to message #338265] |
Mon, 04 August 2008 17:35   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
Another option:
SQL> var val varchar2(40)
SQL> exec :val := '1234,432,123,45322,56786'
PL/SQL procedure successfully completed.
SQL> select wmsys.wm_concat(a) "Ordered" from (
2 select regexp_substr(:val, '[^,]+', 1, level) a
3 from dual
4 connect by regexp_substr(:val, '[^,]+', 1, level) is not null
5 order by to_number(a));
Ordered
---------------------------------------------
123,432,1234,45322,56786
|
|
|
|
|
|
|
|
|
Re: String sorting [message #338527 is a reply to message #338395] |
Tue, 05 August 2008 05:17   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Here's an XMLy modification to Ebrians solution that doesn't involve using undocumented packages.
select trim(',' from regexp_replace(xmltype.getstringval(xmlagg(xmlelement("Val",a))),'<[^[:digit:]]+>',',')) a
from (
select regexp_substr(val, '[^,]+', 1, level) a
from (select '1234,432,123,45322,56786' val from dual) x
connect by regexp_substr(val, '[^,]+', 1, level) is not null
order by to_number(a))
|
|
|
Re: String sorting [message #338729 is a reply to message #338431] |
Tue, 05 August 2008 18:40   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
Littlefoot wrote on Tue, 05 August 2008 02:19 | Brian, Yoda of regular expressions! 
|
Thanks Littlefoot !
Nice option JRowbottom, I was just trying to save some typing with wm_concat 
Another XML solution that can alleviate the additional regexp could be:
SQL> select rtrim (extract (xmlagg (xmlelement ("Z", a ||',')), '/Z/text()'), ',') a
2 from (
3 select regexp_substr(val, '[^,]+', 1, level) a
4 from (select '1234,432,123,45322,56786' val from dual) x
5 connect by regexp_substr(val, '[^,]+', 1, level) is not null
6 order by to_number(a));
A
-------------------------
123,432,1234,45322,56786
Hope I don't forfeit the Yoda status by eliminating a regexp
|
|
|
|
Re: String sorting [message #338889 is a reply to message #338265] |
Wed, 06 August 2008 03:35   |
himang
Messages: 282 Registered: March 2005 Location: Bangalore
|
Senior Member |

|
|
I am getting error while executing Brian's latest solution...
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select trim(',' from regexp_replace(xmltype.getstringval(xmlagg(xmlelement("Val",a))),'<[^[:digit:]]+>',',')) a
2 from (
3 select regexp_substr(val, '[^,]+', 1, level) a
4 from (select '1234,432,123,45322,56786' val from dual) x
5 connect by regexp_substr(val, '[^,]+', 1, level) is not null
6 order by to_number(a)
7 );
A
--------------------------------------------------------------------------------
123,432,1234,45322,56786
SQL> select rtrim (extract (xmlagg (xmlelement ("Z", a ||',')), '/Z/text()'), ',') a
2 from (
3 select regexp_substr(val, '[^,]+', 1, level) a
4 from (select '1234,432,123,45322,56786' val from dual) x
5 connect by regexp_substr(val, '[^,]+', 1, level) is not null
6 order by to_number(a)
7 );
select rtrim (extract (xmlagg (xmlelement ("Z", a ||',')), '/Z/text()'), ',') a
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
This error comes eveytime I execute this query...
|
|
|
|
Re: String sorting [message #338912 is a reply to message #338889] |
Wed, 06 August 2008 04:36   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
himang wrote on Wed, 06 August 2008 10:35 | I am getting error while executing Brian's latest solution...
<snip>
This error comes eveytime I execute this query...
|
Same here.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
|
|
|
|
|
Re: String sorting [message #338947 is a reply to message #338265] |
Wed, 06 August 2008 05:22   |
himang
Messages: 282 Registered: March 2005 Location: Bangalore
|
Senior Member |

|
|
Yes it works for me on 10.2.0.3.0 version.. so may be bug with my local database version...
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> select rtrim (extract (xmlagg (xmlelement ("Z", a ||',')), '/Z/text()'), ',') a
2 from (
3 select regexp_substr(val, '[^,]+', 1, level) a
4 from (select '1234,432,123,45322,56786' val from dual) x
5 connect by regexp_substr(val, '[^,]+', 1, level) is not null
6 order by to_number(a)
7 );
A
--------------------------------------------------------------------------------
123,432,1234,45322,56786
[Updated on: Wed, 06 August 2008 05:23] Report message to a moderator
|
|
|
Re: String sorting [message #338953 is a reply to message #338889] |
Wed, 06 August 2008 05:29   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
I tested it 10.2.0.3, but also works on 10.1.0.4 without using the inner subquery:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for Solaris: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production
SQL> var val varchar2(40)
SQL> exec :val := '1234,432,123,45322,56786'
PL/SQL procedure successfully completed.
SQL> select rtrim (extract (xmlagg (xmlelement ("Z", a ||',')), '/Z/text()'), ',') a
2 from (
3 select regexp_substr(:val, '[^,]+', 1, level) a
4 from dual
5 connect by regexp_substr(:val, '[^,]+', 1, level) is not null
6 order by to_number(a)
7 );
A
-------------------------
123,432,1234,45322,56786
But the bug ( versions < 10.2.0.3) seems to still have problems without a bind variable.
SQL> select * from test;
VAL
------------------------------------
1234,432,123,45322,56786
SQL> select rtrim (extract (xmlagg (xmlelement ("Z", a ||',')), '/Z/text()'), ',') a
2 from (
3 select regexp_substr(val, '[^,]+', 1, level) a
4 from test
5 connect by regexp_substr(val, '[^,]+', 1, level) is not null
6 order by to_number(a)
7 );
ERROR at line 1:
ORA-03113: end-of-file on communication channel
[Updated on: Wed, 06 August 2008 06:04] Report message to a moderator
|
|
|
|
|
|
Re: String sorting [message #582638 is a reply to message #582635] |
Sat, 20 April 2013 09:01   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
There is JRowbottom's query above:
SQL> select trim(',' from regexp_replace(xmltype.getstringval(xmlagg(xmlelement("Val",a))),'<[^[:digit:]]+>',',')) a
2 from ( select regexp_substr(val, '[^,]+', 1, level) a
3 from (select '1234,432,123,45322,56786' val from dual) x
4 connect by regexp_substr(val, '[^,]+', 1, level) is not null
5 order by to_number(a) )
6 /
A
-----------------------------------------------------------------
123,432,1234,45322,56786
Don't know if you call it simpler but it is less lines.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 19:07:24 CST 2025
|