Sort a String and Prepare Range [message #424983] |
Tue, 06 October 2009 23:04  |
m_dk29
Messages: 5 Registered: October 2009
|
Junior Member |
|
|
Hi,
I have huge data which has to be sorted out. I need to prepare a script for range
The data i have is
col1
1
11
2
3
B
C
B12
c1
C123
c2
c3
I need to prepare a script to dispaly the data in the following manner
val('1','3'); -- since 1,2,3 are in range so 1 to 3
val('11'); -- since 11 is not in range of any column
val('B','C'); -- since B,C are in range so B to C
val('B12'); -- since B12 is not in any range
val('c1','c3');
val('C123');
where val , ( , ) are strings which can be concatenated to the data.
I tried converting to ascii and sort it but it doesnt help since some has same starting characters.
Any quick help is appreciated because i need to provide solution ASAP.
Thanks
|
|
|
|
|
|
|
Re: Sort a String and Prepare Range [message #425011 is a reply to message #424983] |
Wed, 07 October 2009 02:58  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, I'll try to start a new trend in this thread, and actually help.
The first thing that you need to do is to get your data sorted correctly.
I'm assuming that your sample data is representative, and that you can have either a numeric string, an alpha string, or an alpha string followed by a numeric string. If this isn't the case, feel free to apologise and post some better data.
create table test_080 (col_1 varchar2(10));
insert into test_080 values ('1');
insert into test_080 values ('11');
insert into test_080 values ('2');
insert into test_080 values ('3');
insert into test_080 values ('B');
insert into test_080 values ('C');
insert into test_080 values ('B12');
insert into test_080 values ('c1');
insert into test_080 values ('C123');
insert into test_080 values ('c2');
insert into test_080 values ('c3');
commit;
select col_1
from test_080
order by regexp_substr(col_1,'^[ [:alpha:] ]+')
,to_number(regexp_substr(col_1,'^[ [:digit:] ]+'))
,col_1;
This query will compare the actual next row with the next row that you'd need if you are in a range:select col_1
,substr(col_1,1,length(col_1)-1)||chr(ascii(substr(col_1,-1))+1) gen_next_col_1
,lead(col_1) over (order by regexp_substr(col_1,'^[ [:alpha:] ]+')
,to_number(regexp_substr(col_1,'^[ [:digit:] ]+'))
,col_1) next_col_1
from test_080
order by regexp_substr(col_1,'^[ [:alpha:] ]+')
,to_number(regexp_substr(col_1,'^[ [:digit:] ]+'))
,col_1;
[trying to fix the Regexps]
[Updated on: Wed, 07 October 2009 03:45] Report message to a moderator
|
|
|