AKASH NAMDEV, January 20, 2017 - 11:59 am UTC
Hi Chris Saxon,
FUNCTION naturalsort
(p_str IN VARCHAR2, p_number_of_zeroes IN NUMBER DEFAULT 4) RETURN VARCHAR2 PARALLEL_ENABLE DETERMINISTIC
IS
tempStr VARCHAR2(4000);
tempChr VARCHAR2(4);
strResult VARCHAR2(4000);
indexN NUMBER;
BEGIN
strResult := '';
IF p_str IS NULL THEN
RETURN '';
ELSIF
length(p_str) = 0 THEN
RETURN '';
END IF;
indexN := 1;
tempStr := '_';
WHILE indexN <= length(p_str) LOOP
tempChr := substr(p_str,indexN,1);
IF ascii(tempChr) < 58 AND ascii(tempChr) > 47 THEN
tempStr := concat(tempStr,tempChr);
ELSIF length(tempStr) > 1 THEN
strResult := concat(strResult,lpad(substr(tempStr,2,p_number_of_zeroes),p_number_of_zeroes,'0'));
tempStr := '_';
strResult := concat(strResult,tempChr);
ELSE
strResult := concat(strResult,tempChr);
END IF;
IF length(tempStr) = p_number_of_zeroes+1 THEN
strResult := concat(strResult,substr(tempStr,2,p_number_of_zeroes));
tempStr := '_';
END IF;
indexN := indexN + 1;
END LOOP;
IF length(tempStr) > 1 THEN
strResult := concat(strResult,lpad(substr(tempStr,2,p_number_of_zeroes),p_number_of_zeroes,'0'));
END IF;
RETURN strResult;
END;
Right now i am using this for natural sorting but it's not fast so i wrote this
order by case when not regexp_like(NAME,'|^([0-9]+)$') then lpad(name,4) else name end ;
result :
Kazenlaan 1
Kazenlaan 11
Kazenlaan 2
but it's not correct it should be like
Kazenlaan 1
Kazenlaan 2
Kazenlaan 11
January 20, 2017 - 4:48 pm UTC
Gah, forgot to to_number the regexp getting the numbers:
with rws as (
select 'Kazenlaan 1' name from dual union all
select 'Kazenlaan 11' from dual union all
select 'Kazenlaan 2' from dual
)
select name from rws
order by regexp_substr(name, '[^0-9]+'), to_number(regexp_substr(name, '[0-9]+'));
NAME
Kazenlaan 1
Kazenlaan 2
Kazenlaan 11
A reader, January 30, 2017 - 6:08 pm UTC
Hi Charis,
Thanks for the reply.