Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SORT by part of filed 2

Status
Not open for further replies.

sal21

Programmer
Apr 26, 2004
411
0
16
IT
Code:
 SELECT DISTINCT LISTINO_BAR.REPARTO FROM REPARTI INNER JOIN LISTINO_BAR ON REPARTI.IDREP = Val(LISTINO_BAR.REPARTO) ORDER BY LISTINO_BAR.REPARTO


but i need to sort from only a numeric value and not to the all in field LISTINO_BAR.REPARTO

note:
the numeric value are dinamic, i can have:

987-....
12-...
0-....

ecc
 
 https://files.engineering.com/getfile.aspx?folder=5538a541-5a38-4070-b312-6d6fc64718d0&file=SCO.jpg
Hi sal21,

You have not given a specific example, so I tried this data
Code:
create or replace table mytab (
  rec character(20)
)
;

insert into mytab 
values
('0555-baz'),
('001-foo'),
('12-bar'),
('9999-foobar')
;

select * from mytab
;

So I have now the table MYTAB with one column REC, which contains the following data
Code:
REC
0555-baz            
001-foo             
12-bar              
9999-foobar

Now, how do I sort the table by the numeric part of the record?
Using CTE (=Common Table Expressions) I create a temporary table with additional column NUMBER, where I extract the numeric string part from the column REC using regular expression function REGEXP_SUBSTR() and convert it into integer number using CAST() , i.e.:
Code:
with mytab_with_number(rec, number) as (
  select
    rec,
    cast(REGEXP_SUBSTR(rec, '\d+', 1, 1) as int) as number
  from mytab
)
select * from mytab_with_number
;

The result I got is this temporary table with the original column REC and additional column NUMBER:
Code:
REC                     NUMBER
0555-baz            	555
001-foo             	1
12-bar              	12
9999-foobar         	9999

Finally, I can sort that table by the NUMBER column:
Code:
with mytab_with_number(rec, number) as (
  select
    rec,
    cast(REGEXP_SUBSTR(rec, '\d+', 1, 1) as int) as number
  from mytab
)
select * from mytab_with_number
order by number
;
and I get the desired result
Code:
REC                     NUMBER
001-foo             	1
12-bar              	12
0555-baz            	555
9999-foobar         	9999
 
tkx micton.
but i cannot test now.
is for Access database?
 
No it's not for MS Access database, I tested it on IBM DB2, I don't have MS Access and don't know if the function REGEXP_SUBSTR() or similar is available on MS Access.
 
If you are sure there will always be a numeric followed by a hyphen, the following should work. I don't have DB2 so I can't test this.
Code:
CODE
SELECT DISTINCT LISTINO_BAR.REPARTO 
  FROM REPARTI 
       INNER JOIN LISTINO_BAR 
          ON REPARTI.IDREP = Val(LISTINO_BAR.REPARTO) 
ORDER BY SUBSTR(LISTINO_BAR.REPARTO ,INSTR(LISTING_BSR.REPARTO,'-')+1);
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top