Numeric Sorting a Character field

[ksr]

I have a Members Table having a column name as "MemberId", which is of varchar2 datatype. The MemberIds contained in the Table generally are "1NRM", "2NRM","3NRM" and so on. Now when I sort the Table on MemberId, the sorting output comes as "1NRM","10NRM","10JDR","100NRM","101NRM","102NRM","1000NRM" and so on. I want the output to be sorted sequentially as "1NRM","2NRM","3NRM","3OST" and so on. How is it possible with a single SQL Statement using functions?

--------------------------------------------------------------------------------

 

[SantaMufasa]

KSR,

Provided that your data continues to resemble the behaviour you describe, then here are some code pieces that do what you want:

Section 1: Function that extracts a numeric value from the numerals embedded in an input string:

create or replace function extract_number (string in varchar2) return number is
begin
   return
      to_number(
          ltrim(
              translate(
                  upper('^'||string), -- upper-cases the string
                      '^ABCDEFGHIJKLMNOPQRSTUVWXYZ','^') -- removes alphas
               ,'^') -- strips added dummy "^" character
               ) -- translates to a number
	       ;
exception
	when others then
		return null -- returns NULL if your values are not well behaved.
		;
end;
/

Section 2: Function that extracts a string of non-numeric characters embedded in an input string:

create or replace function extract_nonnumerics (string in varchar2) return varchar2 is
begin
	return ltrim(translate('^'||string,'^0123456789','^'),'^');
exception
	when others then
		return null;
end;
/

Section 3: Output when not using the functions:

select memberid from members
order by memberid
/
MEMBERID
-----------------
1000NRM
100NRM
101NRM
102NRM
10JDR
10NRM
1NRM
1NRM
20NRM
2NRM
30NRM

11 rows selected.

Section 4:
Output when using the functions to drive sort order:

select memberid from members
order by extract_number(memberid),
         extract_nonnumerics(memberid)
/
MEMBERID
-----------------
1NRM
1NRM
2NRM
10JDR
10NRM
20NRM
30NRM
100NRM
101NRM
102NRM
1000NRM

11 rows selected.

Is this the type of thing you wanted?

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:21 (25Apr04) UTC (aka "GMT" and "Zulu"), 23:21 (24Apr04) Mountain Time)