 Urgent Need help to sort alphanumeric data [message #360233] |
Thu, 20 November 2008 02:22  |
shivabangaru
Messages: 8
Registered: November 2008
Location: Chennai, India
|
Junior Member |
|
|
Hi All,
I have an urgent request which is pending with the following problem.
Pls help me in solving this..
Problem :
I have a table which contains data of various datatypes like alphanumeric,varchar and number.
Now my query is " how to sort the data of the table using alphanumeric field"
How to select the data in a required(MyRequirement) sort order.
Data
============
12.4PI1
12.4
12.2
12.4T
12.3PI1
12.4PI2
12.5PI1
12.4PI3
12.4PI10
12.5T
12.4PI21
12.4PI11
12.4PI20
12.4T1
afterSorting(which I am getting Now)
============
12.2
12.3PI1
12.4
12.4PI1
12.4PI10
12.4PI11
12.4PI2
12.4PI20
12.4PI21
12.4PI3
12.4T
12.4T1
12.5PI1
12.5T1
MyRequirement
===============
12.2
12.3PI1
12.4
12.4PI1
12.4PI2
12.4PI3
12.4PI10
12.4PI11
12.4PI12
12.4PI15
12.4PI20
12.4PI21
12.4PI31
12.4T
12.4T2
12.5PI1
12.5T1
Means it has to sort the data order by lefthand side of PI and also righthand side of PI.
Pls check the attachment if you are not getting the above data in correct order.
Many thanks in Advance
Shiva.
[Updated on: Thu, 20 November 2008 05:31] Report message to a moderator |
|
|
|
| Re: Urgent Need help to sort alphanumeric data [message #360242 is a reply to message #360233] |
Thu, 20 November 2008 03:15  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Does this data have a describabe format?
For example - it looks like you can describe your data as:
2 digits, followed by a period, followed by a single digit, followed by either nothing, or a 'T', or by ('PI' followed by a number of up to 2 digits.
If this is the case, you can use either SUBSTR/INSTR or REGEXP functions to break your data down into it's component part, and then order by them as you wish.
|
|
|
|
|
|
|
|
|
|
| Re: Urgent Need help to sort alphanumeric data [message #360313 is a reply to message #360309] |
Thu, 20 November 2008 06:41 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
For future reference, here's the sort of thing we mean by a test case - it's a create table statement and some insert statements that we can run into a schems and have the data to test solutions.
I've attached some ample code to use REGEXP_SUBSTR to break the string down into it's component parts. I'll leave using these in an Order by up to you.
create table test_023 (col_1 varchar2(100));
insert into test_023 values ('12.4PI1');
insert into test_023 values ('12.4');
insert into test_023 values ('12.2');
insert into test_023 values ('12.4T');
insert into test_023 values ('12.3PI1');
insert into test_023 values ('12.4PI2');
insert into test_023 values ('12.5PI1');
insert into test_023 values ('12.4PI3');
insert into test_023 values ('12.4PI10');
insert into test_023 values ('12.5T');
insert into test_023 values ('12.4PI21');
insert into test_023 values ('12.4PI11');
insert into test_023 values ('12.4PI20');
insert into test_023 values ('12.4T1');
select col_1
,regexp_substr(col_1,'([0-9]+.[0-9]+)')
,regexp_substr(col_1,'([A-Z]{1,2})')
,regexp_substr(col_1,'([0-9]+)$')
from test_023; |
|
|
|
| Re: Urgent Need help to sort alphanumeric data [message #360438 is a reply to message #360313] |
Fri, 21 November 2008 00:17 |
shivabangaru
Messages: 8
Registered: November 2008
Location: Chennai, India
|
Junior Member |
|
|
Thank you very much for your continuous support.
when I am executing this query in TOAD, its giving the following error
select col_1
,regexp_substr(col_1,'([0-9]+.[0-9]+)')
,regexp_substr(col_1,'([A-Z]{1,2})')
,regexp_substr(col_1,'([0-9]+)$')
from test_023;
Its giving below error.
ORA-00904: "REGEXP_SUBSTR": invalid identifier.
-------------------------------------------------------------
select col_1
,substr(col_1,'([0-9]+.[0-9]+)')
,substr(col_1,'([A-Z]{1,2})')
,substr(col_1,'([0-9]+)$')
from test_023;
Its giving below error.
ORA-01722: invalid number
---------------------------------------------------
And also one more information, I am using this query in perl.
Thanks a lot.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Urgent Need help to sort alphanumeric data [message #360474 is a reply to message #360452] |
Fri, 21 November 2008 02:33 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It is fairly unlikely that you don't have SQL*Plus on your box, unless you've got some custom Oracle CLient Install.
Find the location of the Tnsnames.Ora file that TOAD is using, and from there go back up two levels, and into the directory 'bin'
There should be a pair of files in there caled SQLPLUS.EXE and SQLPLUSW.EXE.
Regardless of this, you can find out your current version by
|
|
|
|
| Re: Urgent Need help to sort alphanumeric data [message #360477 is a reply to message #360438] |
Fri, 21 November 2008 02:36 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Interesting,
I get exactly the same error that you get:SQL> select col_1
2 ,regexp_substr(col_1,'([0-9]+.[0-9]+)')
3 ,regexp_substr(col_1,'([A-Z]{1,2})')
4 ,regexp_substr(col_1,'([0-9]+)$')
5 from test_023;
,regexp_substr(col_1,'([0-9]+)$')
*
ERROR at line 4:
ORA-00904: "REGEXP_SUBSTR": invalid identifier
I strongly suspect that you're not using 10g.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Urgent Need help to sort alphanumeric data [message #504622 is a reply to message #504619] |
Tue, 26 April 2011 16:32 |
 |
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
|
Senior Member |
|
|
I don't know what so funny in this. Akashyk is an new member. He might have answered an old post accidentally. Is there any rule in the forum that tells you not to answer old post ? All the members are having right to post reasonable answers to any posting in the forum. Please do not harass new members.
[Updated on: Tue, 26 April 2011 16:36] Report message to a moderator |
|
|
|
|
|
| Re: Urgent Need help to sort alphanumeric data [message #504642 is a reply to message #504622] |
Wed, 27 April 2011 00:54 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Reel your neck in rajavu.
Quote:I don't know what so funny in this. Where did you get your sense of humour bypass? Maybe it can be reversed?
Quote:Akashyk is an new member No he's not. He is an inexperienced member, but he is not a new member. The point is irrelevant anyway. All I did was point out that it was funny.
Quote:Is there any rule in the forum that tells you not to answer old post Did I say that there was? Please point out where I said that he was infringing any rules?
Quote:All the members are having right to post reasonable answers to any posting in the forum. Once again, please point to where I said anything to the contrary.
Quote:Please do not harass new members. It was not my intention to harass, and I do not believe that I did. However, if Akashyk feels that he was harassed then I will apologise to him. Now, rajavu, about that sense of humour bypass...
ETA: Beat me to it Michel
ETA2: I would also point out Rajavu, that what Akashyk posted was not an answer to anything. An answer requires a question, please point out where the question is that Akashyk answered. (Hint: There isn't one). What he did was to repeat a point made by you (and alluded to by several others) several years ago. As Michel said, I would not really consider this a reasonable answer (as it was neither an answer, nor reasonable).
[Updated on: Wed, 27 April 2011 01:07] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
