Home » SQL & PL/SQL » SQL & PL/SQL » String sorting (Oracle 10g)
String sorting [message #338265] Mon, 04 August 2008 06:29 Go to next message
himang
Messages: 281
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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 #338345 is a reply to message #338278] Mon, 04 August 2008 11:44 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1578/0/
Re: String sorting [message #338346 is a reply to message #338345] Mon, 04 August 2008 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing

Was a pure egoistic pleasure. Wink

Regards
Michel
Re: String sorting [message #338395 is a reply to message #338265] Mon, 04 August 2008 17:35 Go to previous messageGo to next message
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 #338414 is a reply to message #338265] Mon, 04 August 2008 23:19 Go to previous messageGo to next message
himang
Messages: 281
Registered: March 2005
Location: Bangalore
Senior Member

Thanks Michel and Ebrian,
Simply amazing both the solutions.
Re: String sorting [message #338431 is a reply to message #338395] Tue, 05 August 2008 01:19 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Brian, Yoda of regular expressions! ./fa/4725/0/
Re: String sorting [message #338460 is a reply to message #338431] Tue, 05 August 2008 03:17 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

you both are simply great

Hets of to you both.... Smile

(Am following you..And one day, i will also be like you.. Smile )

Regards..

[Updated on: Tue, 05 August 2008 03:18]

Report message to a moderator

Re: String sorting [message #338502 is a reply to message #338273] Tue, 05 August 2008 04:39 Go to previous messageGo to next message
himang
Messages: 281
Registered: March 2005
Location: Bangalore
Senior Member

vithalani_dipali wrote on Mon, 04 August 2008 17:30
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..


Dipali - sorry Laughing
Re: String sorting [message #338508 is a reply to message #338502] Tue, 05 August 2008 04:52 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Sorry for what hemang?
I am not..

Got something extra which i didn't have in my knowledge basket.. Smile

Regards,
Dipali..
Re: String sorting [message #338510 is a reply to message #338508] Tue, 05 August 2008 04:53 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
That's the spirit to learn Dipali.Well said

I like that. Smile

Regards,
Rajat
Re: String sorting [message #338527 is a reply to message #338395] Tue, 05 August 2008 05:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Littlefoot wrote on Tue, 05 August 2008 02:19
Brian, Yoda of regular expressions! ./fa/4725/0/

Thanks Littlefoot !

Nice option JRowbottom, I was just trying to save some typing with wm_concat Smile

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 Smile
Re: String sorting [message #338888 is a reply to message #338729] Wed, 06 August 2008 03:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No forfeiture of your green and wrinkly status looks imminent to me.
Re: String sorting [message #338889 is a reply to message #338265] Wed, 06 August 2008 03:35 Go to previous messageGo to next message
himang
Messages: 281
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 #338892 is a reply to message #338889] Wed, 06 August 2008 03:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
ORA-3113's are generally a 'Talk to Support' problem.
Re: String sorting [message #338912 is a reply to message #338889] Wed, 06 August 2008 04:36 Go to previous messageGo to next message
Frank
Messages: 7877
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 #338924 is a reply to message #338912] Wed, 06 August 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But it works in 10.2.0.4, so this was a bug now fixed.

Regards
Michel

[Updated on: Wed, 06 August 2008 04:55]

Report message to a moderator

Re: String sorting [message #338945 is a reply to message #338924] Wed, 06 August 2008 05:16 Go to previous messageGo to next message
rajivgupta780184
Messages: 3
Registered: August 2008
Location: JAMSHEDPUR
Junior Member
you both are simply great

Hets of to you both....
Re: String sorting [message #338947 is a reply to message #338265] Wed, 06 August 2008 05:22 Go to previous messageGo to next message
himang
Messages: 281
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 Go to previous messageGo to next message
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 #582632 is a reply to message #338953] Sat, 20 April 2013 08:19 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Hi Michel sir,

I have the same requirement i.e sorting the string ,I have used your code and It's working fine. is there any simpler one apart from that.

Regards,
Nathan
Re: String sorting [message #582634 is a reply to message #582632] Sat, 20 April 2013 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10.2 or 11.2?

Regards
Michel
Re: String sorting [message #582635 is a reply to message #582634] Sat, 20 April 2013 08:46 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

Sorry Michel sir I have not posted the version, it's Oracle 10g.

Regards,
Nathan
Re: String sorting [message #582638 is a reply to message #582635] Sat, 20 April 2013 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: String sorting [message #582639 is a reply to message #582638] Sat, 20 April 2013 09:34 Go to previous message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

It's more complex than yours ,I'll rather use that code only.

Regards,
Nathan
Previous Topic: query to find the number of alphabets
Next Topic: Separate Index and tables in two different tablespace
Goto Forum:
  


Current Time: Sun Apr 20 14:11:48 CDT 2014

Total time taken to generate the page: 0.09051 seconds