OraFAQ Forum: SQL & PL/SQL » sorting of an alphanumeric value through oracle pl/sql

Home » SQL & PL/SQL » SQL & PL/SQL » sorting of an alphanumeric value through oracle pl/sql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sorting of an alphanumeric value through oracle pl/sql [message #658558]

Mon, 19 December 2016 14:32

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'.

Report message to a moderator

Re: sorting of an alphanumeric value through oracle pl/sql [message #658559 is a reply to message #658558]

Mon, 19 December 2016 14:46

Solomon Yakobson
Messages: 3310
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.

Report message to a moderator

Re: sorting of an alphanumeric value through oracle pl/sql [message #658560 is a reply to message #658559]

Mon, 19 December 2016 15:36

joy_division
Messages: 4963
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.

Report message to a moderator

Re: sorting of an alphanumeric value through oracle pl/sql [message #658573 is a reply to message #658558]

Tue, 20 December 2016 02:03

John Watson
Messages: 9000
Registered: January 2010
Location: Global Village

Senior Member

How about diacritics? What order for

a â æ À

and so on?

Report message to a moderator

Re: sorting of an alphanumeric value through oracle pl/sql [message #658583 is a reply to message #658573]

Tue, 20 December 2016 06:39

Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA

Senior Member

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

SY.

Report message to a moderator

Re: sorting of an alphanumeric value through oracle pl/sql [message #658584 is a reply to message #658583]

Tue, 20 December 2016 06:44

Solomon Yakobson
Messages: 3310
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.

Report message to a moderator

Re: sorting of an alphanumeric value through oracle pl/sql [message #658585 is a reply to message #658584]

Tue, 20 December 2016 06:51

John Watson
Messages: 9000
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

Report message to a moderator

Previous Topic:	DEFAULT Value Behavior
Next Topic:	Convert a sys refcursor into table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 20 12:46:52 CDT 2026