Home » SQL & PL/SQL » SQL & PL/SQL » Sort a String and Prepare Range (Oracle 10g)
Sort a String and Prepare Range [message #424983] Tue, 06 October 2009 23:04 Go to next message
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 #424984 is a reply to message #424983] Tue, 06 October 2009 23:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Sort a String and Prepare Range [message #424986 is a reply to message #424984] Tue, 06 October 2009 23:46 Go to previous messageGo to next message
m_dk29
Messages: 5
Registered: October 2009
Junior Member
BlackSwan, i cant find edit button for my post.

Thanks
Re: Sort a String and Prepare Range [message #424987 is a reply to message #424986] Tue, 06 October 2009 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
m_dk29 wrote on Tue, 06 October 2009 21:46
BlackSwan, i cant find edit button for my post.

Thanks


Just post new/corrected information after this one.
Re: Sort a String and Prepare Range [message #424992 is a reply to message #424983] Wed, 07 October 2009 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Before 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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

What are all the possible values in your column?

Regards
Michel
Re: Sort a String and Prepare Range [message #425011 is a reply to message #424983] Wed, 07 October 2009 02:58 Go to previous message
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

Previous Topic: case statement in a stored procedure
Next Topic: Update statement from 2 table with copied value (merged)
Goto Forum:
  


Current Time: Sat Feb 15 02:26:52 CST 2025