help for a specific sort required.
Bhawna Rajesh, May 14, 2002 - 1:59 am UTC
Hi Tom,
Our client has a column FUNDNUMBER, which is alphanumeric, contains values 0..99, 0A..0Z, 1A..1Z, ..so on, A..Z, A0..A9, B0..B9, ..so on, AA..AZ, BA..BZ,... ZA..ZZ
We are supposed to show the drop-down of FUNDNUMBER in this specific order. The normal orderby on fundnumber works fine except 0..99 range, where the string sorting does not give us desired result. After 1, 11 is shown.
Can we have the resultset retrived in the client-specific order by using some trick?
Please help us out, I know you are the only person who can.
May 14, 2002 - 6:02 am UTC
order by lpad(column,2) to make everything 2 characters long. Consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x varchar2(2) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select rownum from all_users;
36 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select x from t order by x;
X
--
1
10
11
12
13
14
15
16
17
18
19
2
20
21
22
23
24
25
26
27
28
29
3
30
31
32
33
34
35
36
4
5
6
7
8
9
36 rows selected.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select x from t order by lpad( x, 2 );
X
--
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
36 rows selected.
sagi, May 16, 2002 - 7:27 am UTC
You are simply superb with answers are to the point.
Admirer of your knowledge.
Regards
We did the following
Murali, February 20, 2003 - 12:44 pm UTC
create table ting(tno varchar2(4));
insert into ting values('1');
insert into ting values('2');
insert into ting values('3');
insert into ting values('10');
insert into ting values('11');
insert into ting values('1a');
insert into ting values('20');
insert into ting values('2a');
insert into ting values('3a');
insert into ting values('10a');
We expect to see the order of the values as 1,1a,2,2a,3,3a.... and so on with your suggested solution. But what we have seen is the following order after we executed:
select tno from ting order by lpad(tno,4);
tno
----
1
2
3
10
11
1a
20
2a
3a
10a
What can we do to get the desired result?. Please suggest.
February 20, 2003 - 6:53 pm UTC
why? That is not the way character strings sort. think "ascii collating sequence"
ops$tkyte@ORA920> select lpad(tno,4), tno from ting order by lpad(tno,4);
LPAD TNO
---- ----
1 1
2 2
3 3
10 10
11 11
1a 1a
20 20
2a 2a
3a 3a
10a 10a
10 rows selected.
That is just the way that string sorts...
So, it LOOKS to me like you want to sort this field AS IF it were 2 fields yes? A number followed by a string? Sort numerically and within the number by string?
1
1 a
2
2 a
3
3 a
10
10 a
11
20
right? if so, we'll just break it in two:
ops$tkyte@ORA920> select substr(tno,1,length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0)),
2 substr(tno,1+length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0)) ,
3 tno
4 from ting
5 order by
6 to_number(substr(tno,1,length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0))),
7 substr(tno,1+length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0)) NULLS FIRST
8 /
SUBS SUBS TNO
---- ---- ----
1 1
1 a 1a
2 2
2 a 2a
3 3
3 a 3a
10 10
10 a 10a
11 11
20 20
10 rows selected.
And maybe next time think "2 fields = 2 columns. Numbers should be stored in Number types and Strings in varchar2's and dates in dates" and don't store data in such an awkward style ;)
appropriate query for Bhavna's requirement
Chandra, February 21, 2003 - 2:42 am UTC
I think a more appropriate query for the requirement of Bhavna is:
select x from t
order by decode(sign(ascii(substr(lpad(x, 2), 2))-64), 1,
lpad(x, 3, chr(58)), lpad(x, 2));
For eg:-
chandra@ora817> select x from t order by lpad(x, 2);
X
--
1
2
3
4
5
6
7
8
9
A
B
C
0A
0B
0C
10
11
12
13
14
A1
AA
B1
BB
C1
CC
DD
where as
chandra@ora817> select x from t
2 order by decode(sign(ascii(substr(lpad(x, 2), 2))-64), 1,
3 lpad(x, 3, chr(58)), lpad(x, 2));
X
--
1
2
3
4
5
6
7
8
9
10
11
12
13
14
0A
0B
0C
A
B
C
AA
BB
CC
DD
A1
B1
C1
Sorting -- sort a string like a number
A reader, December 09, 2003 - 10:32 am UTC
The last example was perfect for what I needed. Thank you !!
Sort String
Dennis, June 14, 2005 - 7:06 am UTC
These are the sample data for the job tm description column. In that after Licensing Team 1, Licensing Team 10 is displaying
..
It has to come after Licensing Team 3. And Special Firms also.
Job_tm_dscrptn: (Normal query will display given below)
Address Changes
Agent Change
GP/CGA
Licensing Team 1
Licensing Team 10
Licensing Team 2
Licensing Team 3
SRH
Special Firms Team 1
Special Firms Team 10
Special Firms Team 2
Special Firms Team 3
Expected Result
Address Changes
Agent Change
GP/CGA
Licensing Team 1
Licensing Team 2
Licensing Team 3
Licensing Team 10
SRH
Special Firms Team 1
Special Firms Team 2
Special Firms Team 3
Special Firms Team 10
How we can accomplish this???????????????????
June 14, 2005 - 10:02 am UTC
you tell me, describe to me a generic algorithm that can be used on any of your strings in order to break it into N columns that can be sorted (eg: state in text the requirements and the rules)
I mean, anytime the string has a number it should be sorted as a number?
can any string have more string data after the number?
can a string have more than one number in it?
(and most importantly, why wasn't this part of the design.... :)
Alphanumeric Sort
Dennis, June 15, 2005 - 1:24 am UTC
Your Question:
I mean, anytime the string has a number it should be sorted as a number?
can any string have more string data after the number?
can a string have more than one number in it?
My Answer:
I am sorry. String suffixed by number and string with infixed number to be sorted out.
June 15, 2005 - 3:21 am UTC
eh?
more details.... be more "clear", more "precise"
Dennis, June 15, 2005 - 6:58 am UTC
Expected Result
Address Changes
Agent Change
GP/CGA
Licensing Team 1
Licensing Team 2
Licensing Team 3
Licensing Team 10
MN1C
MN2C
MN3C
SRH
Special Firms Team 1
Special Firms Team 2
Special Firms Team 3
Special Firms Team 10
String and Numbers can be infix (MN1C) or suffix (Special Firms Team 10) or just a simple string.
June 15, 2005 - 9:53 am UTC
so, what if you have MN10C as well.
mixed alphanumeric/numeric sort
Urs, June 15, 2005 - 9:18 am UTC
ok, I don't like that either, but it works:
SQL> SELECT t
2 FROM texte
3 ORDER BY t;
T
--------------------------------------------------------------------------------
Agent Change
GP/CGA
Licensing Team 1
Licensing Team 1 ab
Licensing Team 1 b
Licensing Team 10
Licensing Team 1ab
Licensing Team 2
Licensing Team 3
MN1C
MN2C
MN3C
SRH
Special Firms Team 1
Special Firms Team 10
Special Firms Team 2
Special Firms Team 3
17 rows selected.
Elapsed: 00:00:00.32
SQL> SELECT t
2 FROM (SELECT t, startnum, nonumtext, numtext, moretext,
3 DECODE(moretext,
4 NULL, TO_NUMBER(numtext),
5 TO_NUMBER(SUBSTR(numtext, 1, INSTR(numtext, moretext) - 1))) AS numval
6 FROM (SELECT t, startnum, nonumtext, numtext,
7 LTRIM(TRANSLATE(numtext, '0123456789', '0000000000'), '0') AS moretext
8 FROM (SELECT t, startnum, SUBSTR(t, 1, startnum - 1) AS nonumtext,
9 SUBSTR(t, startnum) AS numtext
10 FROM (SELECT t,
11 DECODE(INSTR(TRANSLATE(t, '0123456789', '0000000000'), '0'),
12 0, 9999,
13 INSTR(TRANSLATE(t, '0123456789', '0000000000'), '0'))
14 AS startnum
15 FROM texte))))
16 ORDER BY nonumtext, numval, moretext;
T
--------------------------------------------------------------------------------
Agent Change
GP/CGA
Licensing Team 1
Licensing Team 1 ab
Licensing Team 1 b
Licensing Team 1ab
Licensing Team 2
Licensing Team 3
Licensing Team 10
MN1C
MN2C
MN3C
SRH
Special Firms Team 1
Special Firms Team 2
Special Firms Team 3
Special Firms Team 10
17 rows selected.
Elapsed: 00:00:00.00
hth, Urs
yet another approach
Alexander, June 15, 2005 - 11:59 am UTC
create table a(c varchar2(30))
Table created
insert into a values('asd12341afsdf1wert')
1 row inserted
insert into a values('a9 afsdf1wert')
1 row inserted
insert into a values('a9 af9w 3ert')
1 row inserted
select c, translate(c,'0asdfwert ','0') tr from a
order by to_number(translate(c,'0asdfwert ','0'))
C TR
------------------------------ -------------------------------------------------
a9 afsdf1wert 91
a9 af9w 3ert 993
asd12341afsdf1wert 123411
3 rows selected
drop table a
Table dropped
yet another approach
Alexander, June 15, 2005 - 2:04 pm UTC
create table digits_list(n number(1));
insert into digits_list values(0);
insert into digits_list values(1);
insert into digits_list values(2);
insert into digits_list values(3);
insert into digits_list values(4);
insert into digits_list values(5);
insert into digits_list values(6);
insert into digits_list values(7);
insert into digits_list values(8);
insert into digits_list values(9);
create table power_10(n number(2), p10 number);
insert into power_10 values(1,1);
insert into power_10 values(2,10);
insert into power_10 values(3,100);
insert into power_10 values(4,1000);
insert into power_10 values(5,10000);
insert into power_10 values(6,100000);
insert into power_10 values(7,1000000);
insert into power_10 values(8,10000000);
insert into power_10 values(9,100000000);
insert into power_10 values(10,1000000000);
create table some_values(ph varchar2(9));
insert into some_values values('123a4560a');
insert into some_values values('(123)456-');
insert into some_values values('122.455a5');
insert into some_values values('321-?590a');
select ph, sum(digit_value*p10) cleansed_ph from
(select ph, to_number(substr(ph, n, 1)) digit_value,
row_number() over(partition by ph order by n desc) digit_pos
from some_values join digits_list
on substr(ph, n, 1) between '0' and '9' order by ph, n) digits_val
join power_10 on digit_pos=n
group by ph;
PH CLEANSED_PH
--------- -----------
(123)456- 123456
122.455a5 11224555
123a4560a 11234560
321-?590a 3321590
4 rows selected
drop table digits_list;
drop table power_10;
drop table some_values;
is there any generic solution (that can run on Dbs other than oracle) for this problem?
Rohit Seth, May 18, 2006 - 11:29 am UTC
I also have same requirement, i.e. sorting alphanumeric data like
1
10
PM10
PM11
2
3
and the expected result is
1
2
3
PM10
PM11
Using lpad will surely resolve the issue but is there any other solution that can run on DBs other than oracle (I read that T-SQL doesn't support lpad()), as our application need to support variety of DBs.
thanks
rohit
May 19, 2006 - 9:55 am UTC
good luck with that!
database independence implies you have an application that runs pretty poorly on one database and really really bad on the rest.
And you just made a really good point. Where are you going to find someone that is expert in all N databases so they can come up with mystical, magical, entirely non-performant generic solutions that work exactly the same on all databases? And then hopefully they never get hit by a bus....
order by
A reader, July 19, 2006 - 9:36 am UTC
I have values in a column of number data type as
1,2,3,4,5,6,0,0,0,0
when i select like
select * from t order by x;
0
0
0
1
2
3
4
5
6
are begin displayed
how can i display them as
1
2
3
4
5
6
0
0
0
July 19, 2006 - 1:30 pm UTC
order by decode( col, 0, 1, 0 ), col
Tom Please Help me with this query
Albert, February 12, 2007 - 7:27 am UTC
Tom,
I need help from you to write a complex SQL query (At least for me) here is the explaination
i have 3 tables
change_log,Activity,cdf_line
now cdf_line.cdf_id = activity.cdfid,
activity.activity_id=change_log.table_key_value,
and cdf_line.cdf_id=change_log.table_key_value
what i want to see is when i query records from change_log
i need to group cdf_id of cdf_line table and activity_id of activity table using the link of activity.cdfid=cdf_line.cdf_id
when i query the records i see all of them but value of table_key_value is not grouped with activity_id and its related cdf_id (the link is activity.cdfid=cdf_line.cdf_id)
table_key_value
------------------------
19879S23444 ----------(cdf_line.cdf_id)
190877S3456 ----------(cdf_line.cdf_id)
987Y7651222 ----------(cdf_line.cdf_id)
3478 --(activity.activity_id where cdfid 19879S23444)
9876 --(activity.activity_id where cdfid 190877S3456)
8765 --(activity.activity_id where cdfid 987Y7651222)
what i would like to see it like is
table_key_value
------------------------
19879S23444 ----------(cdf_line.cdf_id)
3478 --(activity.activity_id where cdfid 19879S23444)
190877S3456 ----------(cdf_line.cdf_id)
9876 --(activity.activity_id where cdfid 190877S3456)
987Y7651222 ----------(cdf_line.cdf_id)
8765 --(activity.activity_id where cdfid 987Y7651222)
Is it possible in SQL or am i even making sense to you here?
Thanks in advance
February 12, 2007 - 10:42 am UTC
no creates
no inserts
no look
A reader, February 12, 2007 - 11:37 am UTC
Sorry about that here are the details,
CDF_LINE
Name Null? Type
----------------------------------------- -------- ----------------------------
CDF_ID NOT NULL CHAR(12)
CONTRACT_ID NOT NULL CHAR(8)
SCHEDTYPE NOT NULL CHAR(2)
LINENUMBER NOT NULL CHAR(2)
CATALOGNUMBER NOT NULL CHAR(7)
STATUS CHAR(3)
AUTOANS CHAR(1)
BILLSTART DATE
QUANTITY NUMBER(6)
DEVICECODE VARCHAR2(10)
SALETYPE CHAR(1)
BILLCMF CHAR(8)
LOCATIONCMF CHAR(8)
ONETIME NUMBER(9)
RECURRING NUMBER(9)
CONTACT_ID NOT NULL NUMBER(9)
CONTACT_METHOD_ID NUMBER(9)
LOGON VARCHAR2(20)
WORKSHEETS CHAR(1)
CHECKLIST CHAR(1)
PRODUCTVERSION VARCHAR2(15)
WHOUPDATE VARCHAR2(30)
DATEUPDATE DATE
LINE_SCHEDULE CHAR(4)
GROUP_ID NUMBER(9)
BILLED_FLAG CHAR(1)
MNTONLY CHAR(1)
SURVEY_SCORE NUMBER(5,2)
CREATED_BY VARCHAR2(30)
UPDATED_BY VARCHAR2(30)
CREATE_DATE DATE
ACTIVITY TABLE
Name Null? Type
----------------------------------------- -------- --------------------------
ACTIVITY_ID NOT NULL NUMBER(9)
PROJECT_ID NUMBER(9)
SUBPROJ_ID NUMBER(9)
CDFID CHAR(12)
CONTACT_ID NOT NULL NUMBER(9)
RO CHAR(1)
DESCRIPTION VARCHAR2(100)
ACTIVITY_TYPE_ID NUMBER(9)
NEEDEDBY DATE
PRIORITY CHAR(1)
SCHEDULED CHAR(5)
PHONE CHAR(15)
PASSCODE CHAR(10)
LOCATION VARCHAR2(20)
ESTDATE DATE
ACTDATE DATE
ESTTIME NUMBER(5)
ACTTIME NUMBER(8,2)
ESTDATECHANGED CHAR(1)
MILESTONE CHAR(1)
AFTERHOURS CHAR(1)
COMPLETER VARCHAR2(30)
WHOUPDATE VARCHAR2(30)
DATEUPDATE DATE
CONTACT_METHOD_ID NUMBER(9)
PHASE_ID NOT NULL NUMBER(9)
SEQUENCE NUMBER(3)
GROUP_ID NOT NULL NUMBER(9)
ROLE_ID NOT NULL NUMBER(9)
TRAINING_PARTICIPANTS NUMBER(3)
RELATIVE_DAYS NUMBER(3)
AE_START_DATE DATE
CREATED_BY VARCHAR2(30)
UPDATED_BY VARCHAR2(30)
CREATE_DATE DATE
CHANGE_LOG
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
TABLE_KEY VARCHAR2(30)
CHANGES VARCHAR2(2000)
WHOUPDATE VARCHAR2(30)
DATEUPDATE DATE
TABLE_KEY_VALUE VARCHAR2(50)
CHANGE_LOG_ID NOT NULL NUMBER(9)
here in Change_log table i have few records like
TABLE_KEY_VALUE TABLE_KEY_VALUE
--------------------------------------------------
45020632SW08
300532
208363
45020632SW08
252476
1392318
21302
45020632SW09
300533
in CDF_LINE i have
CDF_ID
------------
38020387SW02
38020387SW03
38020387SW04
38020388SW01
38020389SW01
38020390EQ01
38020390SW01
38020390SW02
38020390SW03
38020391EQ01
and in Activity table i have
ACTIVITY_ID CDFID
----------- ------------
5350227 69033419SW50
5350228 69033419SW51
5350229 69033419SW52
5350230 69033419SW53
5350231 69033419SW54
5350232 69033419SW55
5350233 69033419SW56
5350234 69033419SW57
5350235 69033419SW58
5350236 69033419SW59
Well as it was explained earlier Table_key_value column of change_log table has cdf_id from cdf_line table and activity_id from activity table whose corresponding CDFID is in the same table activity (activity.cdfid=cdf_line.cdf_id)
so as you can see the data is fetched
TABLE_KEY_VALUE
--------------------------------------------------
45020632SW08
300532
208363
45020632SW08
252476
1392318
21302
45020632SW09
300533
what i want is to display table_key_values in such a way where ACTIVITY.ACTIVITY_ID comes first where ACTIVITY.CDFID is followed immediately for that activity_id
like this
table_key_value
-------------------------
252476 (activity.activity_id where activity.cdfid is "45020632SW08")
45020632SW08 (cdf_lines.cdf_id)
1392318(activity.activity_id where activity.cdfid is "45020632SW09")
21302
45020632SW09
300533
208364
Is it possible?
Sorting -- sort a string like a number.
Anand Kumar Barai, July 18, 2011 - 9:53 am UTC
Here is what I have in the database:
SQL> select x from table where x = '28 T1F FTMYFLMA01TTAMPFLXE ' order by x asc;
x
-----
1
10
11
12
13
14
15
16
17
18
19
1F
2
20
21
22
23
24
3
4
5
6
7
8
9
Now I will put the query that will sort the it with alpha numeric first and then the numbers and it did work for me(please see the query with the output:
SQL> select x from table where x = '28 T1F FTMYFLMA01TTAMPFLXE ' order by to_number(translate(x,translate(x,'a0123456789','a'),' ')), lpad(x,5);
x
-----
1F
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
The lpad(x,5) contains the column name x and the number of how many digits you want to filter. Ideally this should be the size of the column x but you can put a lower value also, say if your column is a VARCHAR2(5) then that can be put as 5, but it will yield the proper result even though you put the value as 4 or 3 or 2 as the highest size is 2 for all the values.
July 18, 2011 - 11:18 am UTC
SQL> select x from table where x = '28 T1F FTMYFLMA01TTAMPFLXE ' order by x
asc;
x
-----
1
10
11
that is impossible.
x = '28 T1F ......'
x is not 1, 10, 11 and so on.
none of this makes sense, you need to
a) supply a create table
b) supply inserts into it
c) explain it a lot better.
Just a variant of "sorting by number"?
Duke Ganote, July 18, 2011 - 11:29 am UTC
July 18, 2011 - 11:32 am UTC
I don't know, it was so vague and contradictory - that I skipped it :)
Need help too
Jane, August 15, 2011 - 5:49 pm UTC
I need to sort values to.
3
4
5
6
50A
50
78
124
How to achieve this?
August 16, 2011 - 1:59 am UTC
well, you start by telling us what it means to sort them - what are your rules?
Regexp*2
Brendan, August 23, 2011 - 2:17 am UTC
This small variation on one of the methods in the other thread mentioned might be what you want:
WITH test AS (
SELECT '3' ver FROM DUAL UNION
SELECT '4' FROM DUAL UNION
SELECT '5' FROM DUAL UNION
SELECT '6' FROM DUAL UNION
SELECT '50A' FROM DUAL UNION
SELECT '50' FROM DUAL UNION
SELECT '78' FROM DUAL UNION
SELECT '124' FROM DUAL UNION
SELECT '50A9ZZZZ' FROM DUAL UNION
SELECT '50A10AAA' FROM DUAL
)
SELECT *
FROM test
ORDER BY RegExp_Replace (RegExp_Replace (ver, '(\d+)', '0000000\1.'), '0+(........)\.', '\1');
VER
--------
3
4
5
6
50
50A
50A9ZZZZ
50A10AAA
78
124
10 rows selected.
August 30, 2011 - 3:10 pm UTC
I frankly don't know what his "rules" for sorting are here..
Good/Another Option
Peter, March 29, 2013 - 5:26 pm UTC
I used the order by below to get the result in the num then alpha sort.
Order By Lpad(Regexp_Replace(col, '[[:alpha:]]',''), 10, '0'),
Regexp_Replace(col, '[[:digit:]]','') asc nulls first;
Natural Language Sort Type
Andrew Markiewicz, October 07, 2014 - 6:57 pm UTC
This is later than the original post but I have not seen any recent post about "natural" sorting (ie. numbers and text should sort as users would "expect"). We had a need for this so I created a UDT to effectively split strings on Number|Not Number to generate an array that can sort on each index individually. Comments in the UDT should explain hopefully in full.
Andrew
sql->create or replace type vc_coll as table of varchar2(32767)
2 /
Type created.
Elapsed: 00:00:00.21
sql->
sql->create or replace type nlsort_obj as object
2 (
3 -------------------------------------------------------------------------------
4 -- Program: nlsort_obj
5 -- Description:
6 -- Object type for Natural Language Sort.
7 -- By default, Oracle will sort values in ASCII value order while many times
8 -- users would expect a more natural ordering.
9 -- Ex:
10 -- ASCII NLSort NLSort (case insensitive)
11 -- A1 A1 A1
12 -- A10 A2 a1
13 -- A101 A10 A2
14 -- A2 A101 A10
15 -- a1 a1 a11
16 -- a11 a11 A101
17 --
18 -- nlsort_obj facilitates natural language sorting in SQL by passing the column name to sort.
19 -- Optional parameters can modify the way it will sort.
20 -- The constructor will split the text into array elements looking for two alternating groups.
21 -- "Number" [0-9]
22 -- "Not Number" [^0-9] (referenced as "Alpha" here out)
23 --
24 -- It will stop parsing when both the Number and Alpha search return null.
25 -- The resultant parsing will produce an array of values to use for sorting with most significant
26 -- in the lowest array index. The parser will assume the text begins with Number.
27 --
28 -- Number | Alpha | Number | Alpha | .... | null | null
29 --
30 --
31 -- Constructor parameters:
32 -- self - object instance is implicit in call. Do not provide in constructor call
33 -- self is defined here to use the NOCOPY option to pass by reference.
34 -- p_txt - Text to sort. Typically a column name but any text source can be provided.
35 -- p_caseSensitive - [Y|N] Default Y. Controls whether the comparison will be case sensitive or not
36 -- p_nullsFirst - [Y|N] Default N. Matches Oracle's default. Set to Y to sort null values last.
37 -- This does not affect numeric values delimited with p_numberDelimiter. Numbers
38 -- with p_numberDelimiter text delimiters will sort nulls first always to obtain
39 -- a proper numeric sort.
40 -- p_numberDelimiter - Text delimiter used to denote cascading scale in a number. This will correctly sort
41 -- floating point values (e.g. 10, 10.2, 10.21, etc) or version release numbers
42 -- (12, 12.1.4.1, 12.3.41.0, etc). The delimiter value defaults to '.' but any text delimiter
43 -- up to 50 characters can be used. When the delimiter is recognized in the sort, it will
44 -- automatically sort nulls first regardless of the the p_nullsFirst attribute.
45 -- To remove any numeric delimiter set p_numberDelimiter to null.
46 -------------------------------------------------------------------------------
47 text varchar2(32000)
48 , valArr vc_coll
49 , caseSensitive varchar2(1)
50 , nullsFirst varchar2(1)
51 , numberDelimiter varchar2(50)
52 , order member function sort(p_a in nlsort_obj) return integer
53 , constructor function nlsort_obj
54 (
55 self in out nocopy nlsort_obj
56 , p_txt in varchar2
57 , p_caseSensitive in varchar2 := 'Y'
58 , p_nullsFirst in varchar2 := 'N'
59 , p_numberDelimiter in varchar2 := '.'
60 --, p_checkForDates in varchar2 := 'N'
61 )
62 return self as result
63 );
64 /
Type created.
Elapsed: 00:00:00.01
sql->show err
No errors.
sql->
sql->create or replace type body nlsort_obj
2 as
3 ---------------------------------------------------------------------------
4 order member function sort(p_a in nlsort_obj)
5 return integer
6 is
7 idx integer := 0;
8 ret integer := 0;
9 numChk Boolean := true; --false;
10 numDelimChk Boolean := false;
11 begin
12 --dbms_output.put_line('compare self:p = ' || self.text || ':' || p_a.text);
13
14 --
15 -- Iterate over the elements of alpha and number values in the array
16 -- until an order can be determined for the two comparators
17 --
18 idx := self.valArr.first;
19 while (idx is not null)
20 loop
21 --dbms_output.put_line('idx=' || idx);
22 --dbms_output.put_line('self,p_a=' ||self.valArr(idx) || ',' || p_a.valArr(idx));
23 --dbms_output.put_line('numDelimChk=' || case when numDelimChk then 'True' else 'false' end);
24
25 if not numChk and (self.valArr(idx) = self.numberDelimiter or p_a.valArr(idx) = p_a.numberDelimiter) then
26 numDelimChk := true;
27 end if;
28
29 if self.valArr(idx) is null and p_a.valArr(idx) is null then
30 null; -- indeterminate
31 elsif self.valArr(idx) is null then
32 ret := case when numDelimChk or self.nullsFirst = 'Y' then -1 else 1 end;
33 numDelimChk := false;
34 exit;
35 elsif p_a.valArr(idx) is null then
36 ret := case when numDelimChk or p_a.nullsFirst = 'Y' then 1 else -1 end;
37 numDelimChk := false;
38 exit;
39 elsif numChk then
40 if to_number(self.valArr(idx)) < to_number(p_a.valArr(idx)) then
41 ret := -1;
42 exit;
43 elsif to_number(self.valArr(idx)) > to_number(p_a.valArr(idx)) then
44 ret := 1;
45 exit;
46 else
47 null;
48 end if;
49
50 else
51 if self.valArr(idx) < p_a.valArr(idx) then
52 ret := -1;
53 exit;
54 elsif self.valArr(idx) > p_a.valArr(idx) then
55 ret := 1;
56 exit;
57 else
58 null;
59 end if;
60
61 end if;
62
63 numChk := case when numChk then false else true end;
64 idx := self.valArr.next(idx);
65 end loop;
66 return ret;
67 end;
68
69 ---------------------------------------------------------------------------
70 constructor function nlsort_obj
71 (
72 self in out nocopy nlsort_obj
73 , p_txt in varchar2
74 , p_caseSensitive in varchar2 := 'Y'
75 , p_nullsFirst in varchar2 := 'N'
76 , p_numberDelimiter in varchar2 := '.'
77 --, p_checkForDates in varchar2 := 'N'
78 )
79 return self as result
80 is
81
82 --regText varchar2(21) := '([^0-9]+)[0-9]*';
83 --regNum varchar2(21) := '([0-9]+)[^0-9]*';
84 iter integer := 1;
85 pat1 varchar2(21) := '([0-9]+)[^0-9]*';
86 pat2 varchar2(21) := '([^0-9]+)[0-9]*';
87 pat1Offset integer := 0;
88 begin
89 text := p_txt;
90 valArr := vc_coll();
91 caseSensitive := case when p_caseSensitive in ('Y', 'N') then p_caseSensitive else 'Y' end;
92 nullsFirst := case when p_nullsFirst in ('Y', 'N') then p_nullsFirst else 'N' end;
93 numberDelimiter := substr(p_numberDelimiter, 1,50);
94
95 if caseSensitive = 'N' then
96 text := upper(text);
97 end if;
98
99
100 --
101 -- Always expect p_txt to start with number. If it is Alpha the first element will just be null.
102 -- Iterate over text string until neither pat1 nor pat2 finds a match.
103 -- This will leave two null values at the end of the array which are left intentionally
104 -- to facilitate comparisons of strings with different counts of [Num|Text] pairs.
105 -- Without the nulls, the sort may attempt to use the greater count as the SELF parameter
106 -- and use an index that will be out of bounds for the shorter. The null values at the end
107 -- of the array allow that comparison to happen without error.
108 --
109 loop
110 self.valArr.extend;
111 self.valArr(self.valArr.last) := regexp_substr(
112 self.text,
113 case when iter=1 then '^' else null end ||pat1, 1,iter - pat1Offset,'i',1
114 );
115
116 self.valArr.extend;
117 self.valArr(self.valArr.last) := regexp_substr(self.text, pat2, 1,iter,'i',1);
118
119 exit when valArr(valArr.last) is null and valArr(valArr.last -1) is null;
120
121 if iter =1 and self.valArr(1) is null then
122 --
123 -- This offset is needed when the starting element is a Alpha instead of Number.
124 -- The first element will be null for that value and so the
125 -- first occurrence was not yet found. Without the offset to reduce the match occurrence,
126 -- the last element of this type will not be found and the element will be null while
127 -- pat2 could be found on the same iteration causing issues in correctly sorting.
128 --
129 pat1Offset := 1;
130 end if;
131 iter := iter + 1;
132
133 end loop;
134
135 return;
136 end;
137 end;
138 /
Type body created.
Elapsed: 00:00:00.03
sql->show err
No errors.
sql->
sql->col fld format a30
sql->
sql->----------------------------------------------------------------
sql->-- Test1 - Multiple delimited numeric, e.g. release number
sql->----------------------------------------------------------------
sql->with src as (
2 select '12.3.4.1' fld, 1 srt from dual union all
3 select '12.10.0.1' fld, 2 srt from dual union all
4 select '12.1.4.1' fld, 3 srt from dual union all
5 select '12' fld, 4 srt from dual union all
6 select '12.3.41.0' fld, 5 srt from dual
7 )
8 select * from src
9 order by fld
10 ;
FLD | SRT
------------------------------|----------
12 | 4
12.1.4.1 | 3
12.10.0.1 | 2
12.3.4.1 | 1
12.3.41.0 | 5
Elapsed: 00:00:00.00
sql->
sql->
sql->with src as (
2 select '12.3.4.1' fld, 1 srt from dual union all
3 select '12.10.0.1' fld, 2 srt from dual union all
4 select '12.1.4.1' fld, 3 srt from dual union all
5 select '12' fld, 4 srt from dual union all
6 select '12.3.41.0' fld, 5 srt from dual
7 )
8 select * from src
9 order by nlssort(fld, 'nls_sort=BINARY')
10 ;
FLD | SRT
------------------------------|----------
12 | 4
12.1.4.1 | 3
12.10.0.1 | 2
12.3.4.1 | 1
12.3.41.0 | 5
Elapsed: 00:00:00.00
sql->
sql->
sql->with src as (
2 select '12.3.4.1' fld, 1 srt from dual union all
3 select '12.10.0.1' fld, 2 srt from dual union all
4 select '12.1.4.1' fld, 3 srt from dual union all
5 select '12' fld, 4 srt from dual union all
6 select '12.3.41.0' fld, 5 srt from dual
7 )
8 select * from src
9 order by nlsort_obj(fld)
10 ;
FLD | SRT
------------------------------|----------
12 | 4
12.1.4.1 | 3
12.3.4.1 | 1
12.3.41.0 | 5
12.10.0.1 | 2
Elapsed: 00:00:00.00
sql->
sql->----------------------------------------------------------------
sql->-- Test2 - Mixed alphanumeric and numeric delimiter
sql->----------------------------------------------------------------
sql->with src as (
2 select 'A10.3.4.1' fld, 1 srt from dual union all
3 select '12.10.0.1' fld, 2 srt from dual union all
4 select '12.1.4.1' fld, 3 srt from dual union all
5 select 'A10' fld, 4 srt from dual union all
6 select 'B10' fld, 5 srt from dual union all
7 select 'B1' fld, 6 srt from dual union all
8 select 'B3' fld, 7 srt from dual union all
9 select '12.3.41.0' fld, 8 srt from dual
10 )
11 select s.*
12 from src s
13 order by fld
14 ;
FLD | SRT
------------------------------|----------
12.1.4.1 | 3
12.10.0.1 | 2
12.3.41.0 | 8
A10 | 4
A10.3.4.1 | 1
B1 | 6
B10 | 5
B3 | 7
8 rows selected.
Elapsed: 00:00:00.00
sql->
sql->with src as (
2 select 'A10.3.4.1' fld, 1 srt from dual union all
3 select '12.10.0.1' fld, 2 srt from dual union all
4 select '12.1.4.1' fld, 3 srt from dual union all
5 select 'A10' fld, 4 srt from dual union all
6 select 'B10' fld, 5 srt from dual union all
7 select 'B1' fld, 6 srt from dual union all
8 select 'B3' fld, 7 srt from dual union all
9 select '12.3.41.0' fld, 8 srt from dual
10 )
11 select s.*
12 from src s
13 order by nlsort_obj(fld)
14 ;
FLD | SRT
------------------------------|----------
12.1.4.1 | 3
12.3.41.0 | 8
12.10.0.1 | 2
A10 | 4
A10.3.4.1 | 1
B1 | 6
B3 | 7
B10 | 5
8 rows selected.
Elapsed: 00:00:00.00
sql->
sql->with src as (
2 select 'A10.3.4.1' fld, 1 srt from dual union all
3 select '12.10.0.1' fld, 2 srt from dual union all
4 select '12.1.4.1' fld, 3 srt from dual union all
5 select 'A10' fld, 4 srt from dual union all
6 select 'B10' fld, 5 srt from dual union all
7 select 'B1' fld, 6 srt from dual union all
8 select 'B3' fld, 7 srt from dual union all
9 select '12.3.41.0' fld, 8 srt from dual
10 )
11 select s.*
12 from src s
13 order by nlsort_obj(fld, p_nullsFirst => 'Y')
14 ;
FLD | SRT
------------------------------|----------
A10 | 4
A10.3.4.1 | 1
B1 | 6
B3 | 7
B10 | 5
12.1.4.1 | 3
12.3.41.0 | 8
12.10.0.1 | 2
8 rows selected.
Elapsed: 00:00:00.00
sql->
sql->----------------------------------------------------------------
sql->-- Test3 - change the numeric delimiter
sql->----------------------------------------------------------------
sql->with src as (
2 select '12a3a4a1' fld, 1 srt from dual union all
3 select '12a10a0a1' fld, 2 srt from dual union all
4 select '12a1a4a1' fld, 3 srt from dual union all
5 select '12' fld, 4 srt from dual union all
6 select '12a3a41a0' fld, 5 srt from dual
7 )
8 select * from src
9 order by nlsort_obj(fld)
10 ;
FLD | SRT
------------------------------|----------
12a1a4a1 | 3
12a3a4a1 | 1
12a3a41a0 | 5
12a10a0a1 | 2
12 | 4
Elapsed: 00:00:00.00
sql->
sql->with src as (
2 select '12a3a4a1' fld, 1 srt from dual union all
3 select '12a10a0a1' fld, 2 srt from dual union all
4 select '12a1a4a1' fld, 3 srt from dual union all
5 select '12' fld, 4 srt from dual union all
6 select '12a3a41a0' fld, 5 srt from dual
7 )
8 select * from src
9 order by nlsort_obj(fld, p_numberDelimiter=>'a')
10 ;
FLD | SRT
------------------------------|----------
12 | 4
12a1a4a1 | 3
12a3a4a1 | 1
12a3a41a0 | 5
12a10a0a1 | 2
Elapsed: 00:00:00.00
sql->
sql->----------------------------------------------------------------
sql->-- Test4 - Alph numeric with upper and lower case
sql->----------------------------------------------------------------
sql->with src as (
2 select 'A1' fld, 1 srt from dual union all
3 select 'A10' fld, 2 srt from dual union all
4 select 'A100' fld, 3 srt from dual union all
5 select 'A2' fld, 4 srt from dual union all
6 select 'a1' fld, 5 srt from dual union all
7 select 'a11' fld, 6 srt from dual
8 )
9 select * from src
10 order by fld
11 ;
FLD | SRT
------------------------------|----------
A1 | 1
A10 | 2
A100 | 3
A2 | 4
a1 | 5
a11 | 6
6 rows selected.
Elapsed: 00:00:00.00
sql->
sql->with src as (
2 select 'A1' fld, 1 srt from dual union all
3 select 'A10' fld, 2 srt from dual union all
4 select 'A100' fld, 3 srt from dual union all
5 select 'A2' fld, 4 srt from dual union all
6 select 'a1' fld, 5 srt from dual union all
7 select 'a11' fld, 6 srt from dual
8 )
9 select * from src
10 order by nlsort_obj(fld)
11 ;
FLD | SRT
------------------------------|----------
A1 | 1
A2 | 4
A10 | 2
A100 | 3
a1 | 5
a11 | 6
6 rows selected.
Elapsed: 00:00:00.00
sql->
sql->with src as (
2 select 'A1' fld, 1 srt from dual union all
3 select 'A10' fld, 2 srt from dual union all
4 select 'A100' fld, 3 srt from dual union all
5 select 'A2' fld, 4 srt from dual union all
6 select 'a1' fld, 5 srt from dual union all
7 select 'a11' fld, 6 srt from dual
8 )
9 select * from src
10 order by nlsort_obj(fld, p_caseSensitive=>'N')
11 ;
FLD | SRT
------------------------------|----------
A1 | 1
a1 | 5
A2 | 4
A10 | 2
a11 | 6
A100 | 3
6 rows selected.
Elapsed: 00:00:00.00
sql->
sql->spool off
solution can be tried with regexp_replace function
Vamsi Krishna Vallakavi, April 15, 2015 - 10:32 am UTC
The solution can be written as below
select column,.., from table
order by to_number(nvl(trim(regexp_replace(column,'[A-Za-z]')),0)) asc
Works very well, but not for some instances
Ben Waswa, April 17, 2015 - 9:12 pm UTC
Vamsi, your solution is very good. I do, however, find that it does not work very well for some situations. Think of a list of 8 items e.g. BR00.1, BR00.2, BRBF.1, BR00.5, BRIP.1, BR01.1, BRRF.1, BR01.2 for instance.
Your solution results in this order:
BRRF.1,BR00.1,BRBF.1,BRIP.1,BR00.2,BR00.5,BR01.1,BR02.1
instead of :
BR00.1,BR00.2,BR00.5,BR01.1,BR02.1,BRBF.1,BRIP.1,BRRF.1.
How can you tweak to get the 'natural order'?
ASC/Desc in order by clause
Rajeshwaran, Jeyabal, March 07, 2016 - 12:07 pm UTC
Team,
Currently i am using IF/ELSIF construct to do sorting in ASC and DESC order, is it possible to do that in a single block rather than having IF/ELSIF block to achieve it ?
rajesh@ORA11G> variable x refcursor
rajesh@ORA11G> variable p_in number
rajesh@ORA11G> variable p_sort number
rajesh@ORA11G> exec :p_in :=1; :p_sort := 2;
PL/SQL procedure successfully completed.
rajesh@ORA11G> begin
2 if :p_sort = 1 then
3 open :x for
4 select empno,ename,hiredate,sal from emp
5 order by decode(:p_in,2,ename,
6 1,to_char(empno),
7 3,to_char(hiredate,'yyyymmddhh24miss')) asc ;
8 elsif :p_sort = 2 then
9 open :x for
10 select empno,ename,hiredate,sal from emp
11 order by decode(:p_in,2,ename,
12 1,to_char(empno),
13 3,to_char(hiredate,'yyyymmddhh24miss')) desc ;
14 end if;
15 end;
16 /
PL/SQL procedure successfully completed.
rajesh@ORA11G> print x
EMPNO ENAME HIREDATE SAL
---------- ---------- ---------------------- ----------
7934 MILLER 01/23/1982 12:00:00 AM 1300
7902 FORD 12/03/1981 12:00:00 AM 3000
7900 JAMES 12/03/1981 12:00:00 AM 950
7876 ADAMS 05/23/1987 12:00:00 AM 1100
7844 TURNER 09/08/1981 12:00:00 AM 1500
7839 KING 11/17/1981 12:00:00 AM 5000
7788 SCOTT 04/19/1987 12:00:00 AM 3000
7782 CLARK 06/09/1981 12:00:00 AM 2450
7698 BLAKE 05/01/1981 12:00:00 AM 2850
7654 MARTIN 09/28/1981 12:00:00 AM 1250
7566 JONES 04/02/1981 12:00:00 AM 2975
7521 WARD 02/22/1981 12:00:00 AM 1250
7499 ALLEN 02/20/1981 12:00:00 AM 1600
7369 SMITH 12/17/1980 12:00:00 AM 800
14 rows selected.
rajesh@ORA11G>
March 07, 2016 - 5:42 pm UTC
One way is to have a separate order by clause for each direction. These check the value of :p_sort, e.g.:
SQL> variable p_in number
SQL> variable p_sort number
SQL> exec :p_sort := 2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22
SQL> exec :p_in := 3;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
SQL>
SQL> select empno, ename, hiredate, sal
2 from scott.emp
3 order by
4 case
5 when :p_sort = 1 then
6 decode(:p_in,2,ename,
7 1,to_char(empno),
8 3,to_char(hiredate,'yyyymmddhh24miss'))
9 end,
10 case
11 when :p_sort = 2 then
12 decode(:p_in,2,ename,
13 1,to_char(empno),
14 3,to_char(hiredate,'yyyymmddhh24miss'))
15 end desc;
EMPNO ENAME HIREDATE SAL
---------- ---------- --------- ----------
7876 ADAMS 23-MAY-87 1100
7788 SCOTT 19-APR-87 3000
7934 MILLER 23-JAN-82 1300
7902 FORD 03-DEC-81 3000
7900 JAMES 03-DEC-81 950
7839 KING 17-NOV-81 5000
7654 MARTIN 28-SEP-81 1250
7844 TURNER 08-SEP-81 1500
7782 CLARK 09-JUN-81 2450
7698 BLAKE 01-MAY-81 2850
7566 JONES 02-APR-81 2975
7521 WARD 22-FEB-81 1250
7499 ALLEN 20-FEB-81 1600
7369 SMITH 17-DEC-80 800
14 rows selected.
Need help with varchar2 sort
VD, May 06, 2016 - 1:23 am UTC
Hi Tom,
I have created a test table with a varchar2 field and inserted data into it to replicate a real world set of data I am working with.
create table test_table (str_field VARCHAR2(20) NOT NULL);
insert into test_table (str_field) values ('1.1.7');
insert into test_table (str_field) values ('1.1.8');
insert into test_table (str_field) values ('1.1.9');
insert into test_table (str_field) values ('1.1.10');
insert into test_table (str_field) values ('1.2.1');
insert into test_table (str_field) values ('1.2.2');
insert into test_table (str_field) values ('1.6.1');
insert into test_table (str_field) values ('1.10.1');
insert into test_table (str_field) values ('1.10.2');
insert into test_table (str_field) values ('1.20.1');
When I do the following select I get the sort noted below.
SQL> select str_field from test_table order by LPAD(str_field, 10);
STR_FIELD
--------------------
1.1.7
1.1.8
1.1.9
1.2.1
1.2.2
1.6.1
1.1.10
1.10.1
1.10.2
1.20.1
10 rows selected.
But the sort I need to produce for my work is as follows. i.e. 1.1.10 should be after 1.1.9 and not after 1.6.1.
1.1.7
1.1.8
1.1.9
1.1.10
1.2.1
1.2.2
1.6.1
1.10.1
1.10.2
1.20.1
A little bit more explanation:
If we have a 1.1.1 and a 1.1.1.1, they would need to be listed one after the other before 1.1.2 and so on. The most in the string would be ie. 1.2.3.1 (4 places) never would we have 5 (1.2.3.1.2).
Is there a way to achieve this? If yes, could you give please me suggestions for the same? I would really appreciate your input.
Thank you in advance!
May 06, 2016 - 2:08 am UTC
SQL> drop table test_table purge;
Table dropped.
SQL>
SQL> create table test_table (str_field VARCHAR2(20) NOT NULL);
Table created.
SQL>
SQL> insert into test_table (str_field) values ('1');
1 row created.
SQL> insert into test_table (str_field) values ('1.1');
1 row created.
SQL> insert into test_table (str_field) values ('1.1.7');
1 row created.
SQL> insert into test_table (str_field) values ('1.1.8');
1 row created.
SQL> insert into test_table (str_field) values ('1.1.9');
1 row created.
SQL> insert into test_table (str_field) values ('1.1.10');
1 row created.
SQL> insert into test_table (str_field) values ('1.2.1');
1 row created.
SQL> insert into test_table (str_field) values ('1.2.2');
1 row created.
SQL> insert into test_table (str_field) values ('1.6.1');
1 row created.
SQL> insert into test_table (str_field) values ('1.10.1');
1 row created.
SQL> insert into test_table (str_field) values ('1.10.2');
1 row created.
SQL> insert into test_table (str_field) values ('1.20.1');
1 row created.
SQL> insert into test_table (str_field) values ('1.25.3.4');
1 row created.
SQL> insert into test_table (str_field) values ('1.25.4.1');
1 row created.
SQL>
SQL> select str_field
2 from
3 (
4 select str_field,
5 case when i1 > 0 then substr(str_field,1,i1-1) else str_field end c1,
6 case when i2 > 0 then substr(str_field,i1+1,i2-i1-1)
7 when i1 > 0 then substr(str_field,i1+1) end c2,
8 case when i3 > 0 then substr(str_field,i2+1,i3-i2-1)
9 when i2 > 0 then substr(str_field,i2+1) end c3,
10 case when i3 > 0 then substr(str_field,i3+1) end c4
11 from (
12 select
13 str_field,
14 instr(str_field,'.',1,1) i1,
15 instr(str_field,'.',1,2) i2,
16 instr(str_field,'.',1,3) i3
17 from test_table
18 )
19 )
20 order by lpad(c1,4), lpad(c2,4) nulls first, lpad(c3,4) nulls first, lpad(c4,4) nulls first
21 /
STR_FIELD
--------------------
1
1.1
1.1.7
1.1.8
1.1.9
1.1.10
1.2.1
1.2.2
1.6.1
1.10.1
1.10.2
1.20.1
1.25.3.4
1.25.4.1
14 rows selected.
SQL>
Need help with varchar2 sort (Continued)
VD, May 07, 2016 - 5:19 am UTC
Hi Tom,
Thank you so much for the previous suggestion provided. It works perfectly on it`s own. When I add it along with other order by clauses I have in place in my real world query, I am unable to get it to work. When I wrote to you yesterday, I did not mention about the other sorts I have to also work with. My intention yesterday was to try to incorporate any suggestion you would make, to what I already have in place without making explanation too complicated. But that did not work and now I am trying to explain the whole story.
Please find below my new table and data.
create table new_test_table
(
invoice_id NUMBER NOT NULL,
site VARCHAR2(5) NOT NULL,
str_field_ref VARCHAR2(20) NULL,
str_field_udfa VARCHAR2(20) NULL
);
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.1.7', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.1.8', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.1.9', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.1.10', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.2.1', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.2.2', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.6.1', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.10.1', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.10.2', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.20.1', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '1', 'System');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '2', 'Evaporator');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '11', 'System');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '3', 'Evaporator');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '4', 'Evaporator');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '5', 'Evaporator');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '10', 'Condenser');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '13', 'Other');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '15', 'Other');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '15', 'Other');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '12', 'Salable');
Consider this table as containing values for different invoice_ids. Each invoice has data pertaining to either site `01` or `02` and has a unique invoice_id.
The sorting requirement for my work (a report) is dependent upon, first and foremost, by the value of site.
For site `01`, the sorting order should depend upon str_field_ref which is exactly the scenario I explained yesterday in my question. i.e. str_field_ref field defined in today`s table is exactly as the str_field I defined yesterday. (i.e. If we have a 1.1.1 and a 1.1.1.1 in str_field_ref, they would need to be listed one after the other before 1.1.2 and so on. The most in the string would be ie. 1.2.3.1 (4 places) never would we have 5 (1.2.3.1.2). )
When a record is from site `01`, the value in field str_field_udfa is not important and not at all considered for any sorting purposes.
One other important point I forgot to mention yesterday was that if str_field_ref has a number value without any '.' characters, they should be listed below all of the ones with a '.'. For example, if we had a list like 1.1, 1.1.1, 1.6.1 and 1, then 1 should be displayed after all records 1.1, 1.1.1 & 1.6.1
for e.g. what I need is,
1.1
1.1.1
1.6.1
1
Also there can be nulls in str_field_ref.
For site `02`, the sorting order depends on the value of str_field_udfa. In the report, first records of type `System` should be displayed and then of type `Evaporator`, then `Condenser`, then `Other` and then any others.
To achieve all above requirements, I had set an order by clause as outlined below.
Order By
case
when site = '02' then
(Case When str_field_udfa ='System' Then '1'
When str_field_udfa = 'Evaporator' Then '2'
When str_field_udfa = 'Condenser' Then '3'
When str_field_udfa = 'Other' Then '4'
else '5' end)
end,
case
when site = '01' then
(case When instr(str_field_ref, '.') != 0 then LPAD(str_field_ref, 10) else null end) end,
case
when site='01' then (case When instr(str_field_ref, '.') = 0 then LPAD(str_field_ref, 10) else null end)
end;
If I run the following,
select * from new_test_table
where invoice_id = '124570'
Order By
case
when site = '02' then
(Case When str_field_udfa ='System' Then '1'
When str_field_udfa = 'Evaporator' Then '2'
When str_field_udfa = 'Condenser' Then '3'
When str_field_udfa = 'Other' Then '4'
else '5' end)
end,
case
when site = '01' then
(case When instr(str_field_ref, '.') != 0 then LPAD(str_field_ref, 10) else null end) end,
case
when site='01' then (case When instr(str_field_ref, '.') = 0 then LPAD(str_field_ref, 10) else null end)
end;
I get the following and that is exactly what I need for site `02`,
INVOICE_ID SITE STR_FIELD_REF STR_FIELD_UDFA
---------- ----- -------------------- --------------------
124570 02 1 System
124570 02 11 System
124570 02 2 Evaporator
124570 02 3 Evaporator
124570 02 4 Evaporator
124570 02 5 Evaporator
124570 02 10 Condenser
124570 02 13 Other
124570 02 15 Other
124570 02 15 Other
124570 02 12 Salable
11 rows selected.
If I run the following,
select * from new_test_table
where invoice_id = '124565'
Order By
case
when site = '02' then
(Case When str_field_udfa ='System' Then '1'
When str_field_udfa = 'Evaporator' Then '2'
When str_field_udfa = 'Condenser' Then '3'
When str_field_udfa = 'Other' Then '4'
else '5' end)
end,
case
when site = '01' then
(case When instr(str_field_ref, '.') != 0 then LPAD(str_field_ref, 10) else null end) end,
case
when site='01' then (case When instr(str_field_ref, '.') = 0 then LPAD(str_field_ref, 10) else null end)
end;
I get,
INVOICE_ID SITE STR_FIELD_REF STR_FIELD_UDFA
---------- ----- -------------------- --------------------
124565 01 1.1.7 Case
124565 01 1.1.8 Case
124565 01 1.1.9 Case
124565 01 1.2.1 Case
124565 01 1.2.2 Case
124565 01 1.6.1 Case
124565 01 1.1.10 Case
124565 01 1.10.1 Case
124565 01 1.10.2 Case
124565 01 1.20.1 Case
10 rows selected.
Since this is not what I want when I am working with site `01` as it has 1.1.10 after 1.6.1 instead of after 1.1.9, I tried to incorporate your suggestion from yesterday. In order to make that incorporation easier, I also created a TEST API with one function which can pull a substring of a nth position from string of values delimited by a given delimiter.
For e.g.
Test_Api.Get_Substring_At_Position('3.2.4', 1, '.') will return 3
Test_Api.Get_Substring_At_Position('1.10.3', 2, '.') will return 10
Test_Api.Get_Substring_At_Position('1.10.6', 3, '.') will return 6
Test_Api.Get_Substring_At_Position('5', 1, '.') will return 5
CREATE OR REPLACE PACKAGE Test_Api IS
FUNCTION Get_Substring_At_Position(string_ IN VARCHAR2,
position_ IN NUMBER,
delimeter_ IN VARCHAR2 DEFAULT '.') RETURN VARCHAR2;
END Test_Api;
/
CREATE OR REPLACE PACKAGE BODY Test_Api IS
FUNCTION Get_Substring_At_Position(string_ IN VARCHAR2,
position_ IN NUMBER,
delimeter_ IN VARCHAR2 DEFAULT '.') RETURN VARCHAR2
IS
sub_string_ VARCHAR(1000) := '';
start_pos_ NUMBER := 0;
end_pos_ NUMBER := 0;
length_ NUMBER := 0;
BEGIN
if (position_ <= 0) then
return '';
end if;
if (position_ = 1) then
start_pos_ := 0;
else
start_pos_ := instr(string_ || delimeter_, delimeter_ , 1, position_ - 1);
end if;
end_pos_ := instr(string_ || delimeter_, delimeter_ , 1, position_);
length_ := end_pos_ - start_pos_ - 1;
sub_string_ := substr(string_ || delimeter_, start_pos_ + 1, length_);
return sub_string_;
END Get_Substring_At_Position;
END Test_Api;
/
Following is how I tried to incorporate your suggestion, but I get an error of `ORA-00907: missing right parenthesis` at the comma after (lpad(Test_api.Get_Substring_At_Position(str_field_ref, 1, '.'),4).
select * from new_test_table
where invoice_id = '124565'
Order By
case
when site = '02' then
(Case When str_field_udfa ='System' Then '1'
When str_field_udfa = 'Evaporator' Then '2'
When str_field_udfa = 'Condenser' Then '3'
When str_field_udfa = 'Other' Then '4'
else '5' end)
end,
case
when site = '01' then
(case When instr(str_field_ref, '.') != 0 then
--LPAD(str_field_ref, 10) else null end) end,
(lpad(Test_api.Get_Substring_At_Position(str_field_ref, 1, '.'),4),
lpad(Test_api.Get_Substring_At_Position(str_field_ref, 2, '.'),4) nulls first, lpad(Test_api.Get_Substring_At_Position(str_field_ref, 3, '.'),4) nulls first, lpad(Test_api.Get_Substring_At_Position(str_field_ref, 4, '.'),4) nulls first) else null end) end,
case
when site='01' then (case When instr(str_field_ref, '.') = 0 then LPAD(str_field_ref, 10) else null end)
end;
It looks like when using a case statement for ordering, there cannot be multiple fields to order by with.
Is there anyway, I can incorporate your suggestion while still observing the other sorting requirements for site `02` and site `01`? Or else is there altogether a new way to do this?
Again I highly appreciate any help you can provide on this issue.
May 07, 2016 - 8:20 am UTC
Something like this ?
SQL> select site,str_field_ref, str_field_udfa,
2 case
3 when site = '02' then decode(str_field_udfa,'System','1','Evaporator','2','Condenser','3','Other','4','5')
4 end||
5 case when c2 is not null then lpad(c1,4,'0')||nvl(lpad(c2,4,'0'),'0000')||nvl(lpad(c3,4,'0'),'0000')||nvl(lpad(c4,4,'0'),'0000')
6 else lpad(c1,4,'0')||'9999'
7 end sortkey
8 from
9 (
10 select str_field_ref,site,str_field_udfa,
11 case when i1 > 0 then substr(str_field_ref,1,i1-1) else str_field_ref end c1,
12 case when i2 > 0 then substr(str_field_ref,i1+1,i2-i1-1)
13 when i1 > 0 then substr(str_field_ref,i1+1) end c2,
14 case when i3 > 0 then substr(str_field_ref,i2+1,i3-i2-1)
15 when i2 > 0 then substr(str_field_ref,i2+1) end c3,
16 case when i3 > 0 then substr(str_field_ref,i3+1) end c4
17 from (
18 select
19 str_field_ref,site,str_field_udfa,
20 instr(str_field_ref,'.',1,1) i1,
21 instr(str_field_ref,'.',1,2) i2,
22 instr(str_field_ref,'.',1,3) i3
23 from new_test_table
24 )
25 )
26 order by site,sortkey
27 /
SITE STR_FIELD_REF STR_FIELD_UDFA SORTKEY
----- -------------------- -------------------- -----------------
01 1.1.7 Case 0001000100070000
01 1.1.8 Case 0001000100080000
01 1.1.9 Case 0001000100090000
01 1.1.10 Case 0001000100100000
01 1.2.1 Case 0001000200010000
01 1.2.2 Case 0001000200020000
01 1.6.1 Case 0001000600010000
01 1.10.1 Case 0001001000010000
01 1.10.2 Case 0001001000020000
01 1.20.1 Case 0001002000010000
02 1 System 100019999
02 11 System 100119999
02 2 Evaporator 200029999
02 3 Evaporator 200039999
02 4 Evaporator 200049999
02 5 Evaporator 200059999
02 10 Condenser 300109999
02 13 Other 400139999
02 15 Other 400159999
02 15 Other 400159999
02 12 Salable 500129999
21 rows selected.
Cheers,
Connor
Need help with varchar2 sort (Resolved!!!)
VD, May 09, 2016 - 3:42 pm UTC
Connor,
Your suggestion resolved my sorting dilemma!!! I cannot say how grateful I am.
Thank you so much for your generosity,
VD
May 10, 2016 - 12:59 am UTC
Glad we could help
Need help with varchar2 sort | Alternatives
Rajeshwaran, Jeyabal, May 10, 2016 - 3:08 pm UTC
Sorry for being late in the game, Here are some other alternatives.
rajesh@ORA11G> select t.* ,
2 regexp_replace( regexp_replace(str_field_ref||'.','(\d+\.)','0000\1'),
3 '0+(....)\.','\1') as col3
4 from new_test_table t
5 order by
6 case when site ='01' then 1
7 when site ='02' and str_field_udfa ='System' then 2
8 when site ='02' and str_field_udfa ='Evaporator' then 3
9 when site ='02' and str_field_udfa ='Condenser' then 4
10 when site ='02' and str_field_udfa ='Other' then 5
11 else 6 end , col3
12 /
INVOICE_ID SITE STR_FIELD_REF STR_FIELD_UDFA COL3
---------- ----- -------------------- -------------------- ---------------
124565 01 1.1.7 Case 000100010007
124565 01 1.1.8 Case 000100010008
124565 01 1.1.9 Case 000100010009
124565 01 1.1.10 Case 000100010010
124565 01 1.2.1 Case 000100020001
124565 01 1.2.2 Case 000100020002
124565 01 1.6.1 Case 000100060001
124565 01 1.10.1 Case 000100100001
124565 01 1.10.2 Case 000100100002
124565 01 1.20.1 Case 000100200001
124570 02 1 System 0001
124570 02 11 System 0011
124570 02 2 Evaporator 0002
124570 02 3 Evaporator 0003
124570 02 4 Evaporator 0004
124570 02 5 Evaporator 0005
124570 02 10 Condenser 0010
124570 02 13 Other 0013
124570 02 15 Other 0015
124570 02 15 Other 0015
124570 02 12 Salable 0012
21 rows selected.
rajesh@ORA11G>
Need help with varchar2 sort | Alternatives
VD, May 10, 2016 - 4:05 pm UTC
Thank you for your input Rajesh. I appreciate you taking the time to suggest an alternative.
A reader, November 08, 2018 - 4:02 pm UTC
Create a program in Java that will enable CMD input of 1 String ("ASC” or "DESC”) and 5 numbers. As a result, the numbers will be ordered in ascending or descending order, depending on whether "ASC” or "DESC” has been entered. The case of the String is irrelevant
November 09, 2018 - 2:20 am UTC
This sounds a lot like..... homework.