Skip navigation links

MySQL Forums :: Newbie :: Ordering / sorting strings containing numbers


Advanced Search

Ordering / sorting strings containing numbers
Posted by: Patrick Savelberg ()
Date: February 20, 2010 09:37AM

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

Options: ReplyQuote


Subject Written By Posted
Ordering / sorting strings containing numbers Patrick Savelberg 02/20/2010 09:37AM
Re: Ordering / sorting strings containing numbers laptop alias 02/20/2010 09:59AM
Re: Ordering / sorting strings containing numbers Patrick Savelberg 02/21/2010 08:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.