AJIT VARSHNEY, October 02, 2015 - 10:42 am UTC
Your solution is AMEZING !!!!
I greatly appreciate your -
Superb solution
Quickest response I ever seen
Thank you very much sir for helping me !!!
I appreciate you wonderful thinking and work.
October 03, 2015 - 1:59 am UTC
Thanks for the kind feedback
As Tom might have said
AndyP, October 05, 2015 - 11:34 am UTC
We can achieve the same result without the expense of the regexp function, using standard SQL
with testdata as
(
select column_value from table (sys.odcivarchar2list
('1'
,'10'
,'11'
,'111'
,'1111'
,'11ABC'
,'12'
,'1ABC'
,'2'
,'22'
,'222'
,'22ABC'
,'2ABC'
,'3'
,'4'
,'5'
,'J0501B-T0516T 1 of 1'
,'KBA41901108 1 of 1'
,'PNL18186 1of 3'
,'PNL18186 2 of 3'
,'PNL18186 3 of 3'
,'SM-AHR18'
,'T1GC042TLR10C3A'))
)
select column_value
from testdata
order by case when replace(translate(trim(column_value),'0123456789','0'),'0','') is null then to_number(column_value) end
,column_value
/
October 05, 2015 - 11:52 am UTC
Agreed. As awesome as regexp's are, they can be a CPU nasty especially if run millions of times.
Alternative solution
Rob Wolzak, May 12, 2020 - 3:09 pm UTC
order
by case
when regexp_like(x,'^[0-9]+$')
then lpad(x,20,'0')
else
case
when regexp_like(x,'^[0-9]+.*')
then lpad(x,20,'1')
else lpad(x,20,'2')
end
end
;I needed this distinction to get identical results when executiing from a windows client or from a linux cilient
With the solutions already mentioned a sequence as, 1, 1A, A1 is sorted (in the envirnment i am woriking on) as:
1, 1A, A1 (linux)
1, A1, 1A (windows)
Why the resulta are not identical is not clear to me (the Oracle 12.1 server is also a linux server)
I wanted the linix solution also on windows and therefore I extended the prior solution, as shown above, which makes the windows result linux compliant.
May 13, 2020 - 12:32 am UTC
nice stuff
lahari, June 04, 2020 - 9:09 am UTC
Hi, i have a follow up question. How to sort alphanumeric based on a range. Like i want all the alphanumeric between two user specified values(they are also alphanumeric).
June 05, 2020 - 7:56 am UTC
Test case needed.
Always :-)
alphanumeric sort using limits
lahari, June 05, 2020 - 8:25 am UTC
I am trying to sort using upper and lower limits and display all the values in between. This works for numeric but i am trying to understand how to implement this in alphanumeric.
the col1 is alphanumeric(ex:34SKJD1343,#12NKDJNKD,NKJKL12)
select col1 from table where to_char(TRANSLATE(col1,
''ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'', ''000000000000000000000000001234567890'')) >= ''' || limit1 || ''' and to_char(TRANSLATE(col1,
''ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'', ''000000000000000000000000001234567890'')) <= ''' || limit2 || '''' ||;
Please suggest , thanks.
June 10, 2020 - 1:09 am UTC
Can you give us some sample data (at least 5 or 6 rows) and then what order you expect them to be output in
Display Data as Range
Manish, June 16, 2021 - 5:12 pm UTC
Is it possible to display range on the Data as well
For example data below
1
2
3
4
5
10
11
12
22
111
222
1111
SM-AHR18
PNL18186
PNL18187
PNL18188
T1GC042TLR10C3A
KBA41901108 1 of 1
J0501B-T0516T 1 of 1
1ABC
22ABC
111ABC
22ABC
Is displayed as
1 - 12
22 - 22
111 - 111
222 - 222
1111 - 1111
PNL18186 - PNL18188
....
Let us know
June 22, 2021 - 1:59 am UTC
Alphanumeric sort
Roman10, February 22, 2022 - 6:09 pm UTC
Hi guys. I would appreciate if somebody tell me how to sort my values as I tested the solutions in this topic but none has worked out totaly.
In my case I have 3 types of values as follow :
Numeric : 2, 3...
Alpha suffix values like : 100-A, 100-B...
Alpha prefixed values by 'RUN' like : RUN-120, RUN-121 ... (this type have to be in the bottom)
So my result should be like :
2
3
300
300-A
300-B
301-A
301-B
RUN-20
RUN-21
RUN-40
February 23, 2022 - 2:39 pm UTC
What have you tried and why doesn't it work?