Home » SQL & PL/SQL » SQL & PL/SQL » Sorting VARCHAR2 field
Sorting VARCHAR2 field [message #448442] Tue, 23 March 2010 07:16 Go to next message
IRESHA
Messages: 5
Registered: March 2010
Junior Member
Hi,

Could you please help me to sort data set A,B,1,2,A1,A2,B1,B2,2B,1000 as A,B,1,2,1000,A1,A2,B1,B2,2B.

I tried with LPAD and EXPREG_REPLACE funtion. But it did not work.
Please help.
Re: Sorting VARCHAR2 field [message #448446 is a reply to message #448442] Tue, 23 March 2010 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you explain the order rule.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Tue, 23 March 2010 09:36]

Report message to a moderator

Re: Sorting VARCHAR2 field [message #448456 is a reply to message #448442] Tue, 23 March 2010 08:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
This not to show you that i can do this...
But some one may come with diferent solution.

SQL>  select column_value
  2    from table(in_list('A,B,1,2,A1,A2,B1,B2,2B,1000'))
  3   order by is_numeric(substr(column_value||' ',3)) desc,rownum
  4   /

COLUMN_VALUE
-------------
A
B
1
2
1000
A1
A2
B1
B2
2B

10 rows selected.



So please answer
Quote:
Could explain the order rule.


sriram Smile

[Updated on: Tue, 23 March 2010 08:59]

Report message to a moderator

Re: Sorting VARCHAR2 field [message #448513 is a reply to message #448456] Tue, 23 March 2010 22:11 Go to previous messageGo to next message
IRESHA
Messages: 5
Registered: March 2010
Junior Member
Hi,

There is no specific order rule. :S I just want to sort a column in a table as follows
in PLSQL


Column A

A
B
1
2
22
A1
A2
B1
B2
2B
1000


After sorting

A
B
1
2
22
1000
A1
A2
B1
B2
2B


Following function gives an error in PLSQL .
select column_value
from table(in_list('A,B,1,2,A1,A2,B1,B2,2B,1000'))
order by is_numeric(substr(column_value||' ',3)) desc,rownum

Error:

is_numeric invalid function.
Re: Sorting VARCHAR2 field [message #448514 is a reply to message #448513] Tue, 23 March 2010 22:19 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL>  select dbms_metadata.get_ddl('FUNCTION','IS_NUMERIC') from dual
  2   /

DBMS_METADATA.GET_DDL('FUNCTION','IS_NUMERIC')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FUNCTION "SCOTT"."IS_NUMERIC" (p_strval in varchar2) RETURN
NUMBER
 IS
   l_numval NUMBER;
 BEGIN
   l_numval := TO_NUMBER(p_strval);
   RETURN 1;
 EXCEPTION
   WHEN OTHERS THEN
     RETURN 0;
 END is_numeric;


SQL>  select dbms_metadata.get_ddl('TYPE','T_IN_LIST_TAB') from dual;

DBMS_METADATA.GET_DDL('TYPE','T_IN_LIST_TAB')
--------------------------------------------------------------------------------

  CREATE OR REPLACE TYPE "SCOTT"."T_IN_LIST_TAB" AS TABLE OF VARCHAR2 (4000);




SQL>  select dbms_metadata.get_ddl('FUNCTION','IN_LIST') from dual;

DBMS_METADATA.GET_DDL('FUNCTION','IN_LIST')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FUNCTION "SCOTT"."IN_LIST" (p_in_list  IN  VARCHAR2)
  RETURN t_in_list_tab
AS
  l_tab   t_in_list_tab := t_in_list_tab();---Here i used the type
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    l_tab.extend;
    l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN l_tab;
END;



For more reference...

IN_LIST function

sriram Smile
Re: Sorting VARCHAR2 field [message #448516 is a reply to message #448513] Tue, 23 March 2010 22:34 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
IRESHA wrote on Tue, 23 March 2010 22:11
Hi,

There is no specific order rule.

If there is no such order rule then why you are specifying the given order?

"I assume that First it should display char the Number and Mixed type of char first" is n`t it ?

sriram Smile
Re: Sorting VARCHAR2 field [message #448518 is a reply to message #448516] Tue, 23 March 2010 23:01 Go to previous messageGo to next message
IRESHA
Messages: 5
Registered: March 2010
Junior Member
It should desplay char only records first,then numbers,then mixed ones Smile
sorry i did not get you
Re: Sorting VARCHAR2 field [message #448519 is a reply to message #448518] Tue, 23 March 2010 23:11 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yah thats what i said...
Quote:
it should display char the Number and Mixed type of char first


1)char asc
2)Number asc
3)Mixed again in this
.Start with Char Asc
.Start with Number asc

Right ?
So for the string 'A,B,1,2,A1,A2,B1,B2,2B,1000'
SQL> select column_value
  2  from table(in_list('A,B,1,2,A1,A2,B1,B2,2B,1000'))
  3  order by is_numeric(substr(column_value||' ',3)) desc,rownum;

COLUMN_VALUE
-------------
A
B
1
2
1000
A1
A2
B1
B2
2B

10 rows selected.


So create Your Own method to get the desired output.
And i have already submitted the in_list(and its type too) and is_numeric function source code...
sriram Smile
Re: Sorting VARCHAR2 field [message #448520 is a reply to message #448518] Tue, 23 March 2010 23:29 Go to previous messageGo to next message
IRESHA
Messages: 5
Registered: March 2010
Junior Member
My issue have not solved Confused

Consider following data set
TI
A4
1
C1
1BC
22
D4
A
B

After sorting
A
B
1
22
A4
C1
D4
T1
1BC

I used following query

select report_cost_code
FROM project_sales_price_item_tab
order by is_numeric(substr(report_cost_code||' ',10)) ASC
,rownum;

Here Table: project_sales_price_item_tab
column: report_cost_code
Re: Sorting VARCHAR2 field [message #448538 is a reply to message #448520] Wed, 24 March 2010 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My issue have not solved

Michel Cadot wrote on Tue, 23 March 2010 07:43
Could you explain the order rule.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand


Maybe if you have answered our question your issue would be solved.

Regards
Michel

[Updated on: Wed, 24 March 2010 07:47]

Report message to a moderator

Re: Sorting VARCHAR2 field [message #448544 is a reply to message #448538] Wed, 24 March 2010 01:00 Go to previous messageGo to next message
IRESHA
Messages: 5
Registered: March 2010
Junior Member
I have a table called project_sales_price_item_tab.I want to sort column report_cost_code of this table to sort it ascending order.Letters first,then numbers and then mixed ones(Start with Char Asc and Start with Number asc).report_cost_code column contains following values
Order rule: Letters first,then numbers and then mixed ones
see the attached picture.It contains data in report_cost_code column
  • Attachment: sort.gif
    (Size: 402.01KB, Downloaded 93 times)
Re: Sorting VARCHAR2 field [message #448546 is a reply to message #448544] Wed, 24 March 2010 01:05 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Sorting VARCHAR2 field [message #448564 is a reply to message #448544] Wed, 24 March 2010 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
see the attached picture.It contains data in report_cost_code column

I can't execute an image in SQL*Plus.
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel
Re: Sorting VARCHAR2 field [message #448631 is a reply to message #448564] Wed, 24 March 2010 07:10 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
I am unable to run your Picture in windows too...

sriram Smile
Previous Topic: Help in sql Query .
Next Topic: How to call
Goto Forum:
  


Current Time: Mon Sep 26 21:29:04 CDT 2016

Total time taken to generate the page: 0.06234 seconds