| Sort alphanumberic column [message #387780] |
Fri, 20 February 2009 10:27  |
balji_dxb
Messages: 37
Registered: October 2007
|
Member |
|
|
SELECT * FROM SORT_TEST
order by 1
1
10
A1
20
2
the output should be
1
2
10
20
A1
i tried
SELECT a
FROM SORT_TEST
ORDER BY to_number(regexp_substr(A,'^0-9+')),A but the output that i get is
1
10
2
20
A1
which is wrong
how do i sort the way i want
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Sort alphanumberic column [message #387789 is a reply to message #387788] |
Fri, 20 February 2009 11:12  |
 |
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Michel Cadot wrote on Fri, 20 February 2009 17:33 | From one of your previous post:
| Michel Cadot wrote on Tue, 02 October 2007 06:30 | Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).
...
Regards
Michel
|
Alway post a Test case: create table and insert statements along with the result you want with these data.
Regards
Michel
|
[Updated on: Fri, 20 February 2009 11:12] Report message to a moderator |
|
|
|
| Re: Sort alphanumberic column [message #387790 is a reply to message #387780] |
Fri, 20 February 2009 11:27 |
balji_dxb
Messages: 37
Registered: October 2007
|
Member |
|
|
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
SQL> SELECT * FROM SORT_TEST
2 /
A
----------
1
10
A1
20
2
3
30
SQL> SELECT a
2 FROM SORT_TEST
3 ORDER BY to_number(regexp_substr(A,'^0-9+')),A
4 /
A
----------
1
10
2
20
3
30
A1
7 rows selected.
SQL> desc sort_test
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(10)
the output should be
1
2
3
10
20
30
A1
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Sort alphanumberic column [message #387897 is a reply to message #387812] |
Sat, 21 February 2009 10:52 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Michel Cadot wrote on Fri, 20 February 2009 19:45 | Next time you will have a far most faster answer if you follow the guide; it is not so hard most of the posters succeed in this.
Regards
Michel
|
It is also not so hard to create a one column table and insert 5 rows without the original poster providing a testcase.
Single column results are also very well readable without code-tags.
|
|
|
|
|
|
| Re: Sort alphanumberic column [message #387900 is a reply to message #387897] |
Sat, 21 February 2009 10:57 |
|
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | It is also not so hard to create a one column table and insert 5 rows without the original poster providing a testcase.
|
If it is not why didn't he provide it? If he does not provide it for a simple case, will he provide it for a more complex one next time?
| Quote: | Single column results are also very well readable without code-tags.
|
If he does not do it for a simple one column result, will he for a multiple one if we don't say to do it?
Actually he seems to not be able to do it, in any case.
Regards
Michel
[Updated on: Sat, 21 February 2009 10:57] Report message to a moderator |
|
|
|
| Re: Sort alphanumberic column [message #387902 is a reply to message #387900] |
Sat, 21 February 2009 11:01 |
balji_dxb
Messages: 37
Registered: October 2007
|
Member |
|
|
I have shown the table structure (desc sort_test) and i can insert the values through any means(toad, application etc). so not necessary i should write a insert statement, that is why i gave the values that is available in the table (select a from sort_test).
|
|
|
|
|
|
| Re: Sort alphanumberic column [message #387904 is a reply to message #387902] |
Sat, 21 February 2009 11:02 |
balji_dxb
Messages: 37
Registered: October 2007
|
Member |
|
|
|
and infact the solution that i mentioned i gave within code tags and it is displaying something different than what i typed. so i dont feel i made any mistake, instead of wasting time on this
|
|
|
|
|
|
|
|
|
|
| Re: Sort alphanumberic column [message #387908 is a reply to message #387906] |
Sat, 21 February 2009 11:05 |
balji_dxb
Messages: 37
Registered: October 2007
|
Member |
|
|
For you no, but for us to help you, yes.
Don't you think we want to test what we think to post you instead of posting wrong solution?
i didnt write insert statement to insert values, then how can i give the insert statement
|
|
|
|
|
|
| Re: Sort alphanumberic column [message #387910 is a reply to message #387908] |
Sat, 21 February 2009 11:06 |
|
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| balji_dxb wrote on Sat, 21 February 2009 18:05 |
For you no, but for us to help you, yes.
Don't you think we want to test what we think to post you instead of posting wrong solution?
i didnt write insert statement to insert values, then how can i give the insert statement
|
The way we have to: type on your keyboard.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
