SORT by part of filed 2

[sal21]

 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

 

[mikrom]

Hi sal21,

You have not given a specific example, so I tried this data

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

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.:

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:

REC                     NUMBER
0555-baz            	555
001-foo             	1
12-bar              	12
9999-foobar         	9999

Finally, I can sort that table by the NUMBER column:

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

REC                     NUMBER
001-foo             	1
12-bar              	12
0555-baz            	555
9999-foobar         	9999

 

[2009luca]

tkx micton.
but i cannot test now.
is for Access database?

 

[mikrom]

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.

 

[carp]

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
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);

 

[2009luca]

tks micron and carp