Re: numeric sorting on a char field having a decimal
Jaini Said
Paraphrasing:
I have to do some stupid sorting because my customer has their data
formatted in some cryptic decimal code.
This sucks but here goes my answer.
create function mantissa( s varchar(255) )
returning int with ( not variant) ; set debug file to "debug.out";
if (s like "%.%") then
trace "s=" || s;
while ( substr(s,1,1) <> '.' )
let s = substr(s,2) ;
end while
trace "s=" || s;
-- Skip period
let s = substr(s,2);
else
let s = "0" ;
end if
return s::int ;
end function ;
create function notMantissa( s varchar(255) )
returning int with ( not variant) ; define l int;
if (s like "%.%") then
let l=length(s);
while ( l <> 1 and substr(s,l,1) <> '.' )
let l = l-1;
let s = substr(s,1,l) ;
end while
-- Skip period
if ( l = 1 ) then
let s = "0";
else
let s = substr(s,1,l-1) ;
end if
else
let s = "0" ;
end if
return s::int;
end function ;
update statistics for procedure ;
select
notMantissa("8.40"::char(6)),
notMantissa("8.4"::char(6)),
mantissa("8.40"::char(6)),
mantissa("8.4"::char(6)),
mantissa("888"),
mantissa("888."),
notmantissa("888"),
mantissa(".888"),
notmantissa(".888")
from
systables
where
tabid = 1
;
(constant) 8
(constant) 8
(constant) 40
(constant) 4
(constant) 0
(constant)
(constant) 0
(constant) 888
(constant) 0
create table decimaul(
id serial,
abbinormal char(6)
) ;
insert into decimaul values (0, "7.11") ;
insert into decimaul values (0, "7.23") ;
insert into decimaul values (0, "7.24") ;
insert into decimaul values (0, "7.29") ;
insert into decimaul values (0, "7.34") ;
insert into decimaul values (0, "7.40") ;
insert into decimaul values (0, "7.4") ;
insert into decimaul values (0, "7.45") ;
insert into decimaul values (0, "7.5") ;
insert into decimaul values (0, "8.11") ;
insert into decimaul values (0, "8.23") ;
insert into decimaul values (0, "8.24") ;
insert into decimaul values (0, "8.29") ;
insert into decimaul values (0, "8.34") ;
insert into decimaul values (0, "8.40") ;
insert into decimaul values (0, "8.4") ;
insert into decimaul values (0, "8.45") ;
insert into decimaul values (0, "8.5") ;
select abbinormal, notMantissa(abbinormal), mantissa(abbinormal) fromdecimaul order by 2,3 ;
abbinormal (expression) (expression)
7.4 7 4
7.5 7 5
7.11 7 11
7.23 7 23
7.24 7 24
7.29 7 29
7.34 7 34
7.40 7 40
7.45 7 45
8.4 8 4
8.5 8 5
8.11 8 11
8.23 8 23
8.24 8 24
8.29 8 29
8.34 8 34
8.40 8 40
8.45 8 45
I left some trace statements in the mantissa function after I had accidentally deleted a line. Make sure you delete these before putting them into production.