Home » SQL & PL/SQL » SQL & PL/SQL » SORTING WITH ALPHA-NUMERIC (Oracle Database 10g Enterprise Edition Release 10.1.0.2.0)
| SORTING WITH ALPHA-NUMERIC [message #328408] |
Fri, 20 June 2008 01:41  |
subhadip.chanda
Messages: 64
Registered: May 2007
|
Member |
|
|
hi,
My table structure is like this :-
create table test_tab1(A varchar2(20));
insert into test_tab1 VALUES('AB1.1');
insert into test_tab1 VALUES('AB1.2');
insert into test_tab1 VALUES('AB1.3');
insert into test_tab1 VALUES('AB1.10');
insert into test_tab1 VALUES('AB1.11');
insert into test_tab1 VALUES('ACE1.1');
insert into test_tab1 VALUES('ACE1.12');
insert into test_tab1 VALUES('ACE1.2');
insert into test_tab1 VALUES('ACE1.2E');
insert into test_tab1 VALUES('ACE1.2E1');
insert into test_tab1 VALUES('ACE1.2E2');
insert into test_tab1 VALUES('ACE1.2.1');
insert into test_tab1 VALUES('ACE11.2');
insert into test_tab1 VALUES('ACE1.2.11');
I want to sort the data.and my desire output should be like :-
AB1.1
AB1.2
AB1.3
AB1.10
AB1.11
ACE1.1
ACE1.2
ACE1.12
ACE1.2.1
ACE1.2.11
ACE11.2
ACE1.2E
ACE1.2E1
ACE1.2E2
I tried to sort using
SELECT * FROM test_tab1
ORDER BY SUBSTR(A,1,3),
But not getting desired output. please give any idea...
|
|
|
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328423 is a reply to message #328408] |
Fri, 20 June 2008 02:30  |
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
|
Member |
|
|
Hi,
First of all your desired output is not properly sorted.
You can achieve this by creating function based index as
create index tt_idx on test_tab1
(translate(a,'0123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZ')
||translate(a,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789'))
Output is
1* select * from test_tab1
SQL> /
A
--------------------
AB1.1
AB1.2
AB1.3
AB1.10
AB1.11
ACE1.1
ACE1.12
ACE1.2
ACE1.2E
ACE1.2E1
ACE1.2E2
ACE1.2.1
ACE11.2
ACE1.2.11
--------------------
14 rows selected.
Regards,
MSMallya
[Updated on: Fri, 20 June 2008 02:31] Report message to a moderator |
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328427 is a reply to message #328423] |
Fri, 20 June 2008 02:54 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
@msmallya
You've not got an order by - therefore you don't have an ordered set of results. All your query seems to have done is brought the results back in the order of insert. If I reorder the rows like this, you'll see:create table test_tab1(A varchar2(20));
insert into test_tab1 VALUES('ACE1.2');
insert into test_tab1 VALUES('ACE1.2E');
insert into test_tab1 VALUES('ACE1.2E1');
insert into test_tab1 VALUES('AB1.2');
insert into test_tab1 VALUES('AB1.3');
insert into test_tab1 VALUES('AB1.1');
insert into test_tab1 VALUES('AB1.11');
insert into test_tab1 VALUES('ACE1.1');
insert into test_tab1 VALUES('ACE1.12');
insert into test_tab1 VALUES('AB1.10');
insert into test_tab1 VALUES('ACE1.2E2');
insert into test_tab1 VALUES('ACE1.2.1');
insert into test_tab1 VALUES('ACE11.2');
insert into test_tab1 VALUES('ACE1.2.11');
create index tt1_idx on test_tab1
(translate(a,'0123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZ')
||translate(a,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789'));
SQL> select * from test_tab1;
A
--------------------
ACE1.2
ACE1.2E
ACE1.2E1
AB1.2
AB1.3
AB1.1
AB1.11
ACE1.1
ACE1.12
AB1.10
ACE1.2E2
ACE1.2.1
ACE11.2
ACE1.2.11
14 rows selected.
If you use your Translate as an ORDER BY, it still fails to give the correct order:SQL> select * from test_tab1
2 order by translate(a,'0123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZ')
3 ||translate(a,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789');
A
--------------------
AB1.1
AB1.10
AB1.11
AB1.2
AB1.3
ACE1.1
ACE1.12
ACE1.2.1
ACE1.2.11
ACE1.2
ACE1.2E
ACE1.2E1
ACE1.2E2
ACE11.2 |
|
|
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328448 is a reply to message #328427] |
Fri, 20 June 2008 04:03 |
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
|
Member |
|
|
@JRowBottom
Yes, you are right, it simply reverted back records in an oreder
of insert.
I think Order by clause shall fullfill the requirement
select * from test_tab1
order by
(translate(a,'0123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZ')
||translate(a,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789'))
A
--------------------
AB1.1
AB1.10
AB1.11
AB1.2
AB1.3
ACE1.1
ACE1.12
ACE1.2.1
ACE1.2.11
ACE1.2
ACE1.2E
ACE1.2E1
ACE1.2E2
ACE11.2
Thanx and Regards,
MSMallya
[Updated on: Fri, 20 June 2008 04:04] Report message to a moderator |
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328452 is a reply to message #328448] |
Fri, 20 June 2008 04:25 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Sadly, the order that you return the rows in is somewhat different to the order that the OP wanted them in:Your Order Desired Order
AB1.1 AB1.1
AB1.10 AB1.2
AB1.11 AB1.3
AB1.2 AB1.10
AB1.3 AB1.11
ACE1.1 ACE1.1
ACE1.12 ACE1.2
ACE1.2.1 ACE1.12
ACE1.2.11 ACE1.2.1
ACE1.2 ACE1.2.1
ACE1.2E ACE11.2
ACE1.2E1 ACE1.2E
ACE1.2E2 ACE1.2E1
ACE11.2 ACE1.2E2
|
|
|
|
|
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328501 is a reply to message #328465] |
Fri, 20 June 2008 06:04 |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
If my assumptions are correct and your output example wrong, here's a query:
SQL> def max_fields=10
SQL> with
2 lines as (
3 select level line,
4 case
5 when level <= &max_fields then '[^[:digit:]]+'
6 else '[[:digit:]]+'
7 end format,
8 case
9 when level <= &max_fields then level
10 else level-&max_fields
11 end idx
12 from dual
13 connect by level <= 2*&max_fields
14 ),
15 data as (
16 select a, line,
17 case
18 when line <= &max_fields then regexp_substr(a,format,1,idx)
19 else to_char(to_number(regexp_substr(a,format,1,idx)),'9999990')
20 end part,
21 row_number () over
22 (partition by a order by regexp_instr(a,format,1,idx)) rn
23 from test_tab1, lines
24 where regexp_substr(a,format,1,idx) is not null
25 ),
26 merging as (
27 select a, sys_connect_by_path(part,'/') ord
28 from data
29 where connect_by_isleaf = 1
30 connect by prior rn = rn - 1 and prior a = a
31 start with rn = 1
32 )
33 select a from merging order by ord
34 /
A
--------------------
AB1.1
AB1.2
AB1.3
AB1.10
AB1.11
ACE1.1
ACE1.2
ACE1.2.1
ACE1.2.11
ACE1.2E
ACE1.2E1
ACE1.2E2
ACE1.12
ACE11.2
14 rows selected.
Regards
Michel
[Updated on: Fri, 20 June 2008 06:08] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328613 is a reply to message #328501] |
Fri, 20 June 2008 15:51 |
 |
ebrian
Messages: 2794
Registered: April 2006
|
Senior Member |
|
|
Definitely some uncertainty on the OP's ordering requirements, but based off of Michel's assumptions, another option could be:
SQL> select *
2 from test_tab1
3 order by regexp_substr(a1, '^\D+')
4 , to_number(regexp_substr(a1, '\d+'))
5 , to_number(regexp_substr(a1, '\d+',1, 2)) nulls first
6 , regexp_substr(a1, '[[:alpha:]]+',1, 2) nulls first
7 , to_number(regexp_substr(a1, '\d+',1, 3)) nulls first;
A1
--------------------
AB1.1
AB1.2
AB1.3
AB1.10
AB1.11
ACE1.1
ACE1.2
ACE1.2.1
ACE1.2.11
ACE1.2E
ACE1.2E1
ACE1.2E2
ACE1.12
ACE11.2
14 rows selected. |
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328618 is a reply to message #328613] |
Fri, 20 June 2008 16:31 |
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
|
Junior Member |
|
|
Either change the value to be like:
AB0001.0001
AB0001.0002
AB0001.0003
AB0001.0010
AB0001.0011
ACE0001.0001
ACE0001.0002
ACE0001.0002.0001
ACE0001.0002.0011
ACE0001.0002E
ACE0001.0002E0001
ACE0001.0002E0002
ACE0001.0012
ACE0011.0002
Or create a function that converts the data to be like this and a function based index. Order by that function call.
You would have to decide how the 0 padding required based on expected data.
I'm assuming the E is just a character and not an expression of the number of decimal places.
|
|
|
|
|
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328694 is a reply to message #328679] |
Sat, 21 June 2008 11:05 |
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
|
Junior Member |
|
|
| Michel Cadot wrote on Sat, 21 June 2008 10:57 | You can't index a view but you can index one of the base table.
In addition, the previous answer is meaningless in this "function based index", no index is relevant for ordering your data.
|
You may be right on the function based index. I hadn't tried it and someone else posted to do this in another thread. I knew if it didn't work as stated, it would lead to the solution.
Michel, I thought I was posting to a group of developers who could implement an idea. The idea is to put the data in a format that can be sorted as strings. So for you the idea doesn't work only because it needs a small implementation change from a function based index to a pseudo column?
subhadip.chanda, all that's needed is a column added to the base table and a trigger to populate that column by a call to the function on insert/update. Add that column to the view and use that it to sort.
Ray
[Updated on: Sat, 21 June 2008 11:06] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328974 is a reply to message #328697] |
Mon, 23 June 2008 10:37 |
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
|
Junior Member |
|
|
Michel,
The SQL you provided is <b>truly</b> the most efficient SQL I've ever seen! Please note the sarcasm.
You seem to be acting as though you gave the initial post and have all the facts. If, in your case, you have no ability to create a column in a atable and populate it with a trigger, so be it. Why not find out what capability the poster has?
There's rarely enough info given on posts to see the whole picture. If you have some problem understanding the requirement, you have two choices - ask more OR don't answer.
<b>subhadip.chanda</b> sorry about the bantering. You don't need to create a view if you don't have one. You can do something as simple as add to you query order by myFunction(...
Ray
[Updated on: Mon, 23 June 2008 10:38] Report message to a moderator |
|
|
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #328980 is a reply to message #328978] |
Mon, 23 June 2008 11:14 |
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
|
Junior Member |
|
|
| Michel Cadot wrote on Mon, 23 June 2008 12:03 | Instead of blablabla, post the code you are thinking about. I still don't understand what you want to do.
You have OP table, you have OP data, now post what you think it must be done.
Regards
Michel
|
Oooh! I think someone needs a time out!
|
|
|
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #329011 is a reply to message #328984] |
Mon, 23 June 2008 13:23 |
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
|
Junior Member |
|
|
| Michel Cadot wrote on Mon, 23 June 2008 12:40 | Instead of blablabla, post the code you are thinking about. I still don't understand what you want to do.
You have OP table, you have OP data, now post what you think it must be done.
Regards
Michel
|
ok... you seem to act as though I posted here or that I'm supposed to provide a full code solution. Next time I'll send you a bill.
CREATE OR REPLACE
PACKAGE p_test_tab1
IS
FUNCTION format_for_sort(p_a IN test_tab1.a%TYPE)
RETURN VARCHAR2;
END p_test_tab1;
/
CREATE OR REPLACE
PACKAGE BODY p_test_tab1
IS
FUNCTION format_for_sort(p_a IN test_tab1.a%TYPE)
RETURN VARCHAR2
IS
TYPE sections_tab_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
sections_tab sections_tab_type;
v_return_value VARCHAR2(1000) := '';
v_trimmed_value test_tab1.a%TYPE := p_a;
dotIndex NUMBER;
loop_counter NUMBER := 0;
FUNCTION format_section(p_section IN VARCHAR2)
RETURN VARCHAR2
IS
v_test_number NUMBER;
BEGIN
BEGIN
v_test_number := NVL(p_section, '0');
RETURN LPAD(v_test_number, 5, '0');
EXCEPTION WHEN OTHERS THEN NULL;
END;
RETURN p_section;
END format_section;
PROCEDURE trim_section
IS
v_section VARCHAR2(100);
BEGIN
IF dotIndex = 0 THEN
v_section := v_trimmed_value;
ELSE
v_section := SUBSTR(v_trimmed_value, 1, (dotIndex - 1));
END IF;
--v_trimmed_value := SUBSTR(v_trimmed_value, (dotIndex + 2));
v_trimmed_value := SUBSTR(v_trimmed_value, (LENGTH(v_section) + 2));
sections_tab(sections_tab.COUNT + 1) := format_section(v_section);
END trim_section;
PROCEDURE separate_sections
IS
BEGIN
LOOP
dotIndex := INSTR(v_trimmed_value, '.', 1);
trim_section;
EXIT WHEN dotIndex = 0;
loop_counter := loop_counter + 1;
EXIT WHEN loop_counter > 5;
END LOOP;
END separate_sections;
PROCEDURE build_return
IS
BEGIN
FOR sectionIndex IN 1..sections_tab.COUNT LOOP
IF sectionIndex > 1 THEN
v_return_value := v_return_value || '.';
END IF;
v_return_value := v_return_value || sections_tab(sectionIndex);
END LOOP;
END build_return;
BEGIN
separate_sections;
build_return;
RETURN v_return_value;
END format_for_sort;
END p_test_tab1;
/
select a
from test_tab1
order by p_test_tab1.format_for_sort(a)
/
|
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #329012 is a reply to message #329011] |
Mon, 23 June 2008 13:34 |
|
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I don't know if your solution works (I don't even know if it compiles) but I have to say:
1/
It is NOT formated and so unreadable. Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
2/
| Quote: | EXCEPTION WHEN OTHERS THEN NULL;
|
This is one of the worst things, no the worst thing you can do in PL/SQL programming.
3/
"LPAD(v_test_number, 5, '0')", don't you know TO_CHAR function?
4/
Remove useless empty lines, it does not clear the text and lead to a several pages procedure hard to follow
5/
Post the SQL*Plus session showing it works.
6/
Do you think it is better than a SQL solution?
7/
Where is the function based index?
8/
Where is the view?
9/
Where is the added column?
10/
Where is the trigger?
Regards
Michel
[Updated on: Mon, 23 June 2008 13:36] Report message to a moderator |
|
|
|
| Re: SORTING WITH ALPHA-NUMERIC [message #329024 is a reply to message #329012] |
Mon, 23 June 2008 14:09 |
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
|
Junior Member |
|
|
| Michel Cadot wrote on Mon, 23 June 2008 14:34 | I don't know if your solution works (I don't even know if it compiles)
|
I have to hope you're capable of finding out if a package compiles or not. What exactly is your problem? You complain on and on.
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
1/
It is NOT formated and so unreadable.)
|
At least you gave some helpful comments as to how to better post answers. Thanks for that. Otherwise see above.
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
2/
| Quote: | EXCEPTION WHEN OTHERS THEN NULL;
|
This is one of the worst things, no the worst thing you can do in PL/SQL programming.
|
I'm going to code a quick response and yes there may be some clean-up necessary in the poster's final deliverable code. This is more than enough to get him started. Besides that, show me in this case how it will fail to work.
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
3/
"LPAD(v_test_number, 5, '0')", don't you know TO_CHAR function?.
|
Don't you know LPAD function?
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
4/
Remove useless empty lines, it does not clear the text and lead to a several pages procedure hard to follow
|
See above.
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
5/
Post the SQL*Plus session showing it works.
|
You've tried this bully approach to get me to provide the code. YOU show ME it doesn't work.
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
6/
Do you think it is better than a SQL solution?
|
Yes.
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
7/
Where is the function based index?
|
Follow the thread.
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
8/
Where is the view?
|
Follow the thread.
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
9/
Where is the added column?
|
Follow the thread.
| Michel Cadot wrote on Mon, 23 June 2008 14:34 |
10/
Where is the trigger?
|
Follow the thread.
I agreed with you that the function based index would not help in the order by.
You pushed the idea that a developer could not create columns, triggers and functions. I told you that the idea was sound and that the implementation could change depending on the capabilities of the developer. You then challenge me for an actual working solution. I supply you with one AND YOU'RE NOT EVEN THE ONE POSTING THE THREAD!
You've torn apart the thread started by subhadip.chanda. For what gain? Is it your plan to torment all users on this site for your own pleasure? If you see yourself as the almighty god of Oracle, I hope your making millions. If you don't have privillege to create columns and triggers, I doubt it.
From now on I know of one person's comment to ignore. The purpose of forums like this is to help others not use it as your personal flame site.
[Updated on: Mon, 23 June 2008 14:15] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Sep 08 01:44:31 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
