Ordering / sorting strings containing numbers

[Patrick Savelberg]

Hi,

I searched a lot on the forum to find a solution to my sorting problem. The problem is that I have multiple records that contain article numbers which sould be retrieved as follows:

article 1
article 1.1
article 1.2
article 1.3
article 1.4
article 1.4a
article 1.4b
article 1.5
...
article 1.10
article 1.11
article 1.11.1
...
article 1.40
...
article 100

using a normal order by you get this order:

article 1
article 100
article 1.1
article 1.10
article 1.11
article 1.11.1
article 1.2
article 1.3
article 1.4
article 1.40
article 1.4a
article 1.4b
article 1.5
...

Since I found no solution I started to build a stored function that adds zeroes before every sequence of numbers. EG 'article 1.12.5' contains three numerical parts: 1 12 5. Depending on how many positions each numerical part can contain I add zeroes in a way that 'article 1.12.5' is substituted with 'article 001.012.005'. Using this notation a normal order by gives me the desired result. Since I want my records to be ordered straight away in my query, without any additional external ordering (PHP)code or adding an extra column to order on, I use this stored function in my order by clause:

CREATE FUNCTION add_zeroes_to_numbers_for_sorting(string_value VARCHAR(30), max_positions INT) RETURNS VARCHAR(100) NO SQL
BEGIN
DECLARE current_position INT(3) DEFAULT 1;
DECLARE value VARCHAR(100) DEFAULT '';
DECLARE string_value_reverse VARCHAR(30) DEFAULT REVERSE(string_value);
DECLARE string_value_length INT(3) DEFAULT CHAR_LENGTH(string_value);
DECLARE current_char CHAR(1);
DECLARE last_char_is_number BOOLEAN DEFAULT FALSE;
DECLARE number_count INT(3) DEFAULT 0;

loop_label: LOOP
IF current_position > string_value_length THEN
LEAVE loop_label;
END IF;

SET current_char = SUBSTR(string_value_reverse, current_position, 1);
IF ASCII(current_char) BETWEEN 48 AND 57 THEN
SET value = CONCAT(value, current_char);
SET last_char_is_number = TRUE;
SET number_count = number_count + 1;
ELSE
IF last_char_is_number THEN
set value = CONCAT(value, REPEAT(0, max_positions - number_count));
set number_count = 0;
END IF;
SET last_char_is_number = FALSE;
IF current_char = '' THEN
SET value = CONCAT(value, '\ ');
ELSE
SET value = CONCAT(value, current_char);
END IF;

END IF;

SET current_position = current_position + 1;
END LOOP loop_label;

IF last_char_is_number THEN
set value = CONCAT(value, REPEAT(0, max_positions - number_count));
END IF;

RETURN (REVERSE(value));

END


The query could look like this:
SELECT title
FROM article
ORDER BY add_zeroes_to_numbers_for_sorting(title, 3);

I hope this will help some of you out there struggling on the same issue. Any improvement suggestions are very welcome.

Thank you,
Patrick Savelberg