Home » Other » Training & Certification » how to order char and number and varchar2
how to order char and number and varchar2 [message #262031] Fri, 24 August 2007 06:36 Go to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
how to order a field containing
number , varchar,char and date
in correct sequence
help me
my requirement is

1) All routes that start with a number should be sorted numerically. Where a route starts with a number but ends with a letter these should be first sorted numerically and then alphanumerically. e.g. 1, 2, 3, 3a, 3c, 4, 10, 11, 31a, 87, 112
2) Following routes that start numerically should be routes that start with a letter. The exception to this is those routes that begin with 'N' which are dealt with in point 3. Routes that start with a letter should be sorted as follows. e.g. D1, D2, D10, E1, E2, E10, ELS, F1, F2, G4, M1, O2.
3) Finally routes that begin with 'N' should be listed. e.g. N1, N2, N3, N5, N7, N8, N9, N10, N11, N13.

Report message to a moderator

Re: how to order char and number and varchar2 [message #262036 is a reply to message #262031] Fri, 24 August 2007 06:42 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
What did you come up yourself so far? Looks like a fairly easy homework assignment to me.
No SQL Experts necessary, so moving to the Homework section

Report message to a moderator

Re: how to order char and number and varchar2 [message #262041 is a reply to message #262036] Fri, 24 August 2007 06:49 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
 
select x from t
order by decode(sign(
                length(
                nvl(
                translate(x,'0123456789','')
                ,0)
                )),
                0,
                to_number(x),
                to_char(x))


[Mod-edit: added code tags]

[Updated on: Fri, 24 August 2007 06:56] by Moderator

Report message to a moderator

Re: how to order char and number and varchar2 [message #262046 is a reply to message #262041] Fri, 24 August 2007 06:55 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
"Reported message"
Reported By: vikeshcool@hotmail.com On: Fri, 24 August 2007 13:53 In: Other » Homework, Training & Certification » how to order char and number and varchar2
Reason hi i need this answer urgently kindly look my homework and reply


Don't do that. Reporting messages is meant for abusive or offensive messages, not to get attention.

[Updated on: Fri, 24 August 2007 06:56]

Report message to a moderator

Re: how to order char and number and varchar2 [message #262047 is a reply to message #262041] Fri, 24 August 2007 06:58 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
What happens if you do a simple order by? Which elements fail? Work from there.
You have not provided any create table / insert statements, so I cannot test anything

[Updated on: Fri, 24 August 2007 06:58]

Report message to a moderator

Re: how to order char and number and varchar2 [message #262050 is a reply to message #262047] Fri, 24 August 2007 07:01 Go to previous messageGo to next message
vikeshcool@hotmail.com
Messages: 27
Registered: August 2007
Junior Member
create table t ( x varchar2(30))
/

insert into t values('15')
/

insert into t values('1')
/

insert into t values('6')
/

insert into t values('9')
/

insert into t values('1A')
/

insert into t values('1a')
/

insert into t values('B12')
/

insert into t values('b12')
/

insert into t values('N222')
/

insert into t values('n2N')
/

i want these in order first number then character
and then small character shoul come first before
big one

Report message to a moderator

Re: how to order char and number and varchar2 [message #262090 is a reply to message #262050] Fri, 24 August 2007 08:17 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
 
SQL> select * from t order by x;

X
------------------------------
1
15
1A
1a
6
9
B12
N222
b12
n2N

10 rows selected.

Like I said: do an ordinary order by and see what needs to be done.
Looks like the only items out of line are the N%

So, order by <first letter, N or not N> then x

Edit: to add for the uppercase-lowercase add a "case when x = upper(x) then ....." to the order by

[Updated on: Fri, 24 August 2007 08:19]

Report message to a moderator

Re: how to order char and number and varchar2 [message #262097 is a reply to message #262090] Fri, 24 August 2007 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unfortunatly this depends on the country:
SQL>  select * from t order by x;
X
------------------------------
B12
b12
n2N
N222
1
1A
1a
15
6
9

10 ligne(s) sélectionnée(s).

But you can:
SQL> select * from t order by nlssort(x,'NLS_SORT=BINARY');
X
------------------------------
1
15
1A
1a
6
9
B12
N222
b12
n2N

10 ligne(s) sélectionnée(s).

Regards
Michel

Report message to a moderator

Re: how to order char and number and varchar2 [message #262115 is a reply to message #262031] Fri, 24 August 2007 10:09 Go to previous message
transfer
Messages: 53
Registered: August 2007
Location: transfer
Member
To create the table, use Tom Kyte's "variable in list technique" (asktom.oracle.com) :
variable instring varchar2(256);
begin
:instring := '1, 2, 3, 3a, 3c, 4, 10, 11, 31a, 87, 112';
:instring := :instring || ', D1, D2, D10, E1, E2, E10, ELS, F1, F2, G4, M1, O2, ';
:instring := :instring || 'N1, N2, N3, N5, N7, N8, N9, N10, N11, N13';
end;
/
drop table routes;
create table routes (route varchar2(16));
insert into routes
with STR2TBL as
 (select trim(substr(TXT, 
                     INSTR(TXT, ',', 1, level) + 1, 
                     INSTR(TXT, ',', 1, level + 1) - INSTR(TXT, ',', 1, level) -1)) as TOKEN
 from (select ',' || :instring || ',' TXT from DUAL) where rownum >= 0
 connect by level <= length(:instring) -length(replace(:instring, ',', '')) + 1)
select token route from STR2TBL;
My actual answer does just what the original post asked for, neither more nor less. The only cases there were of type '9', 'A', '9A' or 'A9' but not '9A9' or 'A9A'.
with step1 as (
select route, 
case when substr(route, 1, 1) >= '0' and substr(route, 1, 1) <= '9' then 0
     when substr(upper(route), 1, 1) = 'N' then 2
else 1 end sort_rule_1,
to_number(translate(upper(route), '0ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0')) sort_num,
translate(route, 'A0123456789', 'A') sort_alpha
from routes)
select route, sort_rule_1,
case sort_rule_1 when 0 then sort_num else null end sort_num_1,
sort_alpha,
case sort_rule_1 when 0 then null else sort_num end sort_num_2
from step1
order by sort_rule_1, sort_num_1 nulls last, sort_alpha nulls first, sort_num_2 nulls first;

ROUTE            SORT_RULE_1 SORT_NUM_1 SORT_ALPHA       SORT_NUM_2
---------------- ----------- ---------- ---------------- ----------
1                          0          1
2                          0          2
3                          0          3
3a                         0          3 a
3c                         0          3 c
4                          0          4
10                         0         10
...
N9                         2            N                         9
N10                        2            N                        10
N11                        2            N                        11
N13                        2            N                        13

[Updated on: Fri, 24 August 2007 10:26]

Report message to a moderator

Previous Topic: compare string?
Next Topic: difference b/w Sql & Sql * Plus commands
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 01:50:55 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.