Home » SQL & PL/SQL » SQL & PL/SQL » Urgent Need help to sort alphanumeric data (Oracle10g)
icon9.gif  Urgent Need help to sort alphanumeric data [message #360233] Thu, 20 November 2008 02:22 Go to next message
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 Go to previous messageGo to next message
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 #360243 is a reply to message #360233] Thu, 20 November 2008 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please 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) and align the columns in result.
Use the "Preview Message" button to verify.

Also always post your Oracle version (4 decimals) and a test case: create table and insert statements along with the result you want with these data..

In the end, this has been asked many times, what searches did you do before posting?

Regards
Michel
Re: Urgent Need help to sort alphanumeric data [message #360299 is a reply to message #360243] Thu, 20 November 2008 06:07 Go to previous messageGo to next message
shivabangaru
Messages: 8
Registered: November 2008
Location: Chennai, India
Junior Member

Thank you michel,

this is the first time I am posting for a forum.
will follow your guidelines.

Regards,
shiva.
Re: Urgent Need help to sort alphanumeric data [message #360309 is a reply to message #360242] Thu, 20 November 2008 06:32 Go to previous messageGo to next message
shivabangaru
Messages: 8
Registered: November 2008
Location: Chennai, India
Junior Member

Hi JRowbottom,

Thanks for the quick response.

What you understand is correct.

It may be like 12.4PI or 12.4PI1 or 12.4PI12 or 12.4 or 12.3T .
Now we have data up to 12.4PI11 and 12.5PI1.
In future it may be listed like 12.10PI1,12.12PI2..

Previously what happened is, the code was written for supporting from 1 to 9(i.e single digit.ex:12.4PI1 to 12.4PI9) after PI or T. Now the requirement is for two digits.

I will be happy, if you can give me some example query to sort the data.




Regards,
Shiva.

Re: Urgent Need help to sort alphanumeric data [message #360313 is a reply to message #360309] Thu, 20 November 2008 06:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #360444 is a reply to message #360233] Fri, 21 November 2008 00:50 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Are you sure that you are using Oracle 10g ?

What is the Version of your database ?

Smile
Rajuvan.
Re: Urgent Need help to sort alphanumeric data [message #360449 is a reply to message #360444] Fri, 21 November 2008 01:04 Go to previous messageGo to next message
shivabangaru
Messages: 8
Registered: November 2008
Location: Chennai, India
Junior Member

Hi Rajuvan,

Thankyou very much for responding for my query.

Yes I am using Oracle 10g.
And I am executing the query in TOAD.

Thanks Again,

Shiva.
Re: Urgent Need help to sort alphanumeric data [message #360451 is a reply to message #360449] Fri, 21 November 2008 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
quoting Ana (anacedent):
Those who live by the GUI, die by the GUI.


Use SQL*Plus and retry and copy and paste your session including client (SQL*Plus banner) and server version (v$version).

Regards
Michel

[Updated on: Fri, 21 November 2008 01:12]

Report message to a moderator

Re: Urgent Need help to sort alphanumeric data [message #360452 is a reply to message #360451] Fri, 21 November 2008 01:21 Go to previous messageGo to next message
shivabangaru
Messages: 8
Registered: November 2008
Location: Chennai, India
Junior Member

Hi Michel,

we dont have sql*plus.
we have provided with TOAD only.


Thanks,
Shiva.
Re: Urgent Need help to sort alphanumeric data [message #360474 is a reply to message #360452] Fri, 21 November 2008 02:33 Go to previous messageGo to next message
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
SELECT *
FROM   v$version;
Re: Urgent Need help to sort alphanumeric data [message #360477 is a reply to message #360438] Fri, 21 November 2008 02:36 Go to previous messageGo to next message
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
but only when I run my code against our old Oracle 9i box.

I strongly suspect that you're not using 10g.
Re: Urgent Need help to sort alphanumeric data [message #360502 is a reply to message #360474] Fri, 21 November 2008 03:41 Go to previous messageGo to next message
shivabangaru
Messages: 8
Registered: November 2008
Location: Chennai, India
Junior Member

Thanks friend,

Below I am providing the total information regarding the version,
which i got with your query.


SELECT *
FROM   v$version;



BANNER

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

Re: Urgent Need help to sort alphanumeric data [message #360515 is a reply to message #360502] Fri, 21 November 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it 9.2 and not 10 as you claimed.
Do you think we ask for version number just to bore you?

Regards
Michel
Re: Urgent Need help to sort alphanumeric data [message #360525 is a reply to message #360233] Fri, 21 November 2008 04:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And more specifically ,

Regular Expressions were introduced in 10g and it wont work in Oracle 9i.

Now what you have to do is to create Functions to return the three parts of the string (12.4PI10 as 12.4 ,'PI,10) and select order by .

Smile
Rajuvan.
Re: Urgent Need help to sort alphanumeric data [message #360527 is a reply to message #360515] Fri, 21 November 2008 04:54 Go to previous messageGo to next message
shivabangaru
Messages: 8
Registered: November 2008
Location: Chennai, India
Junior Member

sorry for my mistake michel.

Re: Urgent Need help to sort alphanumeric data [message #504605 is a reply to message #360233] Tue, 26 April 2011 13:57 Go to previous messageGo to next message
Akashyk
Messages: 2
Registered: November 2007
Location: Boston USA
Junior Member

Of course. Regex is not supported by Oracle 9 and below.
Re: Urgent Need help to sort alphanumeric data [message #504606 is a reply to message #504605] Tue, 26 April 2011 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you woke up here after your 3.5 years last post just to repeat what has been said 2.5 years ago?
I think you should go back to sleep.

Regards
Michel

[Updated on: Wed, 27 April 2011 00:49]

Report message to a moderator

Re: Urgent Need help to sort alphanumeric data [message #504619 is a reply to message #504605] Tue, 26 April 2011 15:51 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
LOL, that is utterly bizarre. Genuinely funny, thanks, I'll share this one with my colleagues.
Re: Urgent Need help to sort alphanumeric data [message #504622 is a reply to message #504619] Tue, 26 April 2011 16:32 Go to previous messageGo to next message
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 #504640 is a reply to message #504622] Wed, 27 April 2011 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Akashyk is an new member

A new member? he registered in May 2007! How many times are we still a new member?
Reasonable answer? Repeating a previous answer is NOT a reasonable answer.

Quote:
Please do not harass new members

So no one did it.

Regards
Michel
Re: Urgent Need help to sort alphanumeric data [message #504642 is a reply to message #504622] Wed, 27 April 2011 00:54 Go to previous message
pablolee
Messages: 2835
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

Previous Topic: Query help
Next Topic: Bulk collect
Goto Forum:
  


Current Time: Fri Dec 09 03:51:18 CST 2016

Total time taken to generate the page: 0.05810 seconds