Home » SQL & PL/SQL » SQL & PL/SQL » Sorting Alphanumeric values in Varchar2 column
Sorting Alphanumeric values in Varchar2 column [message #252656] Thu, 19 July 2007 10:13 Go to next message
karthi_real
Messages: 11
Registered: May 2007
Junior Member
Hi all,
I created a table with Varchar2 field, which has Alphanumeric values. i tried sorting, but i did not get the expected results.

I have given the data in the table, the result i got, and the result i need to get,

Without space in the data

Data -----After Sorting ----- Way I need
A1 ----- A1 ----- A1
A2 ----- A10 ----- A2
A11 ----- A11 ----- A3
A14 ----- A14 ----- A10
A22 ----- A2 ----- A11
A25 ----- A21 ----- A14
A3 ----- A22 ----- A21
A21 ----- A25 ----- A22
A10 ----- A3 ----- A25

With Space in the data

Data ----- After sorting
A1 ----- A 21
A2 ----- A1
A1 1 ----- A1 1
A14 ----- A10
A22 ----- A14
A2 5 ----- A2
A3 ----- A2 5
A 21 ----- A22
A10 ----- A3

I tried using to_number in order by, since char field has character values it's not working.
I tried using SUBSTR in order by clause, even that is not yielding proper results in the way i need.
I tried using NLS_SORT and NLS_COMP environment variables,
first i did not know whether i have used these values properly
(I want to implement this sort in webbased application).


Is there any other way of doing this sort?? if so could you tell me the way of doing that??

Thanks for your understanding
Re: Sorting Alphanumeric values in Varchar2 column [message #252665 is a reply to message #252656] Thu, 19 July 2007 10:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Referring to your example, did you try
ORDER BY SUBSTR(col, 1, 1), TO_NUMBER(SUBSTR(col, 2, LENGTH(col)));
Re: Sorting Alphanumeric values in Varchar2 column [message #252767 is a reply to message #252656] Thu, 19 July 2007 22:39 Go to previous messageGo to next message
karthi_real
Messages: 11
Registered: May 2007
Junior Member
thanks for your reply,

I did not try, after seeing your reply i tried.
yes, it works when the data in the column has no spaces.

but, it did not work when there is some data with spaces.

For the sample data, i listed in the previous message under heading DATA WITH SPACES(Ex - A1 4, A2 5, A24 5), and if the data has _ character(Ex - A_1, A1_1, A24_45).

thanks for your understanding.
Re: Sorting Alphanumeric values in Varchar2 column [message #252796 is a reply to message #252767] Fri, 20 July 2007 01:00 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
So, some of the data has spaces in it, some has underscores and some has more than one number in it (A24_45), right?
First, you will have to figure out how you want the sort to be, how would sort this A24_45 value?
Let's say we want to sort on the character first (A, B, etc.) and then on any number in there, regardless of spaces and/or underscores or any other character that show up. I suggest you look up REPLACE and TRANSLATE in the SQL reference.
Re: Sorting Alphanumeric values in Varchar2 column [message #252816 is a reply to message #252656] Fri, 20 July 2007 02:10 Go to previous messageGo to next message
karthi_real
Messages: 11
Registered: May 2007
Junior Member
yes, some data might have charactervalue or alphanumeric value or numeric value.
i need to sort the data in the order, numeric values has to come first and alphanumeric (alphanumeric values has to be sorted in the order, where character and number have to be in order. Ex- ABC123, ABC124, ADA 345,B1, CD23) and character values.

thank you. i do look into SQL reference.

If anybody know how to do it, could you tell me how to do it??

with accordance to Littlefoot reply,
the ORDER BY clause will work if the column has one character, it will not work if the data has more than one character folowing by number or if the data has only character values and if at all there is any numeric value then the order of sorting wil be different.
Re: Sorting Alphanumeric values in Varchar2 column [message #252818 is a reply to message #252816] Fri, 20 July 2007 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
order by substr(col,1,instr(translate(col,'1234567890','##########'),'#')-1),
         to_number(translate(substr(col,instr(translate(col,'1234567890','##########'),'#')),'0 _','0'))

Regards
Michel
Re: Sorting Alphanumeric values in Varchar2 column [message #252842 is a reply to message #252656] Fri, 20 July 2007 04:22 Go to previous messageGo to next message
karthi_real
Messages: 11
Registered: May 2007
Junior Member
Thanks Michel,

I have tried this ORDER BY clause, but it shows ORA -01722 Invalid Number error when i tried to sort this data,

A1, A2, A3, C11 345, b356_123, b2_31, a_2_DF1, B 2_cr3, A 1_SR4.

Could you tell me why it shows error??

thanks in advance.
Re: Sorting Alphanumeric values in Varchar2 column [message #252845 is a reply to message #252842] Fri, 20 July 2007 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't say there could be non numeric characters in the second part, that is after the first number. You only told about space or underscore.

For me, it was fields are "N characters (N>0) followed by a digit then a number of digits or space or underscore". For order, 2 subfields: "the first string part then a number built with the second part ignoring spaces and underscores".

If you change the specifications, the solution changes.
What are the full and definitive specifications?
Explain it as I did.
What is the order of "A 1_SR4", "A 1_RW4", "A 1_SR5"... and why?

Regards
Michel

[Updated on: Fri, 20 July 2007 08:28]

Report message to a moderator

Re: Sorting Alphanumeric values in Varchar2 column [message #252906 is a reply to message #252842] Fri, 20 July 2007 10:21 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
The approach in the following query may help you craft a solution.

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 20 11:17:17 2007

create table t ( c varchar2(10) );

insert into t
select * from table
( varchar2_1000_varray_type
  ( 'A1', 'A2', 'A3', 'C11 345', 'b356_123', 'b2_31', 'a_2_DF1', 'B 2_cr3', 'A 1_SR4' )
);

select * from t order by
  regexp_replace( c, '[[:digit:]]', '~' ),
  regexp_replace( c, '[[:alpha:]]', '~' )
;

C
----------
A 1_SR4
A1
A2
A3
B 2_cr3
C11 345
a_2_DF1
b2_31
b356_123



--
Joe Fuda
SQL Snippets
Re: Sorting Alphanumeric values in Varchar2 column [message #252907 is a reply to message #252906] Fri, 20 July 2007 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good try but:
SQL> with t as (select 'A 1_SR4' c from dual union all select 'A 2_RW4' from dual)
  2  select * from t order by
  3    regexp_replace( c, '[[:digit:]]', '~' ),
  4    regexp_replace( c, '[[:alpha:]]', '~' )
  5  ;
C
-------
A 2_RW4
A 1_SR4

2 rows selected.

Is "A 2" before "A 1"?

Regards
Michel
Re: Sorting Alphanumeric values in Varchar2 column [message #252924 is a reply to message #252907] Fri, 20 July 2007 11:23 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
I wasn't suggesting a final solution. The OP doesn't give enough information for that. I was just demonstrating some code they may be able to use/adapt for a final solution.
Re: Sorting Alphanumeric values in Varchar2 column [message #252926 is a reply to message #252924] Fri, 20 July 2007 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Was just kidding. Smile
I thought you have open a nice way and I was a little bit jealous. Embarassed

Regards
Michel


Re: Sorting Alphanumeric values in Varchar2 column [message #252929 is a reply to message #252926] Fri, 20 July 2007 12:55 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Fair enough. It's all good. Smile

I'll admit I'm a little guilty of the same. You made a good point and I got all defensive about it.

Cheers bud!
Re: Sorting Alphanumeric values in Varchar2 column [message #253165 is a reply to message #252656] Mon, 23 July 2007 00:52 Go to previous messageGo to next message
karthi_real
Messages: 11
Registered: May 2007
Junior Member
First of all sorry for the delay in response.

It was my mistake Michel, i should have mentioned the specification.

As you asked about the specification, I do not have the data i have to sort.
i tried to get a solution for some combinations of specifications, so that i can use it for the eventual data.

Regarding the order, i do not know actual order, to verify with the sorted order.

Thanks joe and michel for your reply.
Re: Sorting Alphanumeric values in Varchar2 column [message #253194 is a reply to message #252656] Mon, 23 July 2007 01:50 Go to previous messageGo to next message
karthi_real
Messages: 11
Registered: May 2007
Junior Member
Ia m sorry once agian.

I should have told this long before am using Oracle 9i in that Regular_exp will not work.

Could you tell me is there any other way to handle non numeric characters after numeric as in that example??

thanks in advance
Re: Sorting Alphanumeric values in Varchar2 column [message #253197 is a reply to message #253194] Mon, 23 July 2007 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Study and modify the solution I gave.
As we don't have the full and clear specification we can't waste our time to find a non-working solution.

Regards
Michel
Re: Sorting Alphanumeric values in Varchar2 column [message #253296 is a reply to message #252656] Mon, 23 July 2007 07:25 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
How can you even start to write code without having your specifications from the functional people that will be using your solution ?
Re: Sorting Alphanumeric values in Varchar2 column [message #253472 is a reply to message #252656] Tue, 24 July 2007 01:30 Go to previous message
karthi_real
Messages: 11
Registered: May 2007
Junior Member
My lead asked me to look for the possibilities of sorting data without writing function in PL/SQL.

I have the data samples which exist in the database, so using that samples i tried to find a solution to sort the data.

Data sample in the database - X1_STORE,X2_STORE,R1_STORE_A,A_STORE.
by taking this sample data, i tried to sort - A1_DF123, A1_ABC, ABC12_345.
I have taken all possible samples and i am trying to do sorting for those sample data.

i did sort with this data, since i do not want to use data in the database.

i thought, if i able to find a solution for this data samples, so that i can use the solution to the existing data.

Regards,
Karthi
Previous Topic: Shell scripting through PL/SQL
Next Topic: Inline Cursors
Goto Forum:
  


Current Time: Thu Dec 08 14:12:14 CST 2016

Total time taken to generate the page: 0.05358 seconds