Home » SQL & PL/SQL » SQL & PL/SQL » sorting of an alphanumeric value through oracle pl/sql
sorting of an alphanumeric value through oracle pl/sql [message #658558] Mon, 19 December 2016 14:32 Go to next message
PR22
Messages: 1
Registered: December 2016
Junior Member
I want to sort an alphanumeric value through oracle pl/sql.
Say, I have a value like '55AKAlogo3D9HJ' and I need the result to be like '3559AADgHJKloo'.
Re: sorting of an alphanumeric value through oracle pl/sql [message #658559 is a reply to message #658558] Mon, 19 December 2016 14:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
Registered: January 2010
Location: Connecticut, USA
Senior Member
Does it have to be PL/SQL? Is it a single string or table column. For a single string:

SQL> with t as (
  2             select '55AKAlogo3D9HJ' str from dual
  3            )
  4  select  listagg(substr(str,level,1)) within group(order by substr(str,level,1)) sorted_str
  5    from  t
  6    connect by level <= length(str)
  7  /

SORTED_STR
------------------------------------------------------------------------------------------------
3559AADHJKgloo

SQL> 

SY.
Re: sorting of an alphanumeric value through oracle pl/sql [message #658560 is a reply to message #658559] Mon, 19 December 2016 15:36 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
I would just wrap an UPPER function around the substring order in Solomon's solution as it seems OP is ignoring case for his expected output.
Re: sorting of an alphanumeric value through oracle pl/sql [message #658573 is a reply to message #658558] Tue, 20 December 2016 02:03 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
How about diacritics? What order for
a â æ À
and so on?
Re: sorting of an alphanumeric value through oracle pl/sql [message #658583 is a reply to message #658573] Tue, 20 December 2016 06:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
Registered: January 2010
Location: Connecticut, USA
Senior Member
order by nlssort(substr(str,level,1),'nls_sort=icelandic')

SY.
Re: sorting of an alphanumeric value through oracle pl/sql [message #658584 is a reply to message #658583] Tue, 20 December 2016 06:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually:

order by nlssort(substr(str,level,1),'nls_sort=icelandic_ci')

since OP wanted case-insensitive sort.

SY.

Re: sorting of an alphanumeric value through oracle pl/sql [message #658585 is a reply to message #658584] Tue, 20 December 2016 06:51 Go to previous message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
What Oracle needs is a Scottish sort order. Most librarians consider the Mc and Mac prefixes to be equal, and place them before M and any other letter, like this:

McDonald
MacKay
Mabel

I wonder if the Locle Builder is up to it. There's a pointless topic for the next OOW Smile
Previous Topic: DEFAULT Value Behavior
Next Topic: Convert a sys refcursor into table
Goto Forum:
  


Current Time: Tue Jan 16 20:10:51 CST 2018

Total time taken to generate the page: 0.01720 seconds