How the NATURAL_SORT_KEY() function works in Mariadb?

The NATURAL_SORT_KEY() function is a string function that returns a binary string that can be used to sort strings in a natural order.

Posted on

The NATURAL_SORT_KEY() function is a string function that returns a binary string that can be used to sort strings in a natural order. A natural order is a way of sorting strings that considers numerical parts as numbers, rather than as characters. For example, in a natural order, file2.txt comes before file10.txt, whereas in a lexicographical order, file10.txt comes before file2.txt. This function is useful for sorting file names, version numbers, or other strings that contain numerical parts.

Syntax

The syntax of the NATURAL_SORT_KEY() function is as follows:

NATURAL_SORT_KEY(str [, pad_length])

The function takes two arguments:

  • str: A string that needs to be sorted in a natural order. This argument is mandatory.
  • pad_length: An integer that specifies the length of padding for the numerical parts. This argument is optional. If omitted, the default value of 10 is used.

The function returns a binary string that can be used to sort the input string in a natural order. The function works by splitting the input string into alphanumeric and non-alphanumeric parts, and then padding the numerical parts with zeros to the specified length. The function then concatenates the padded parts and returns the result.

Examples

Example 1: Using NATURAL_SORT_KEY() to sort file names

In this example, we use the NATURAL_SORT_KEY() function to sort file names in a natural order. We use the SELECT statement to display the result.

-- Create a table with file names
CREATE TABLE files (
  name VARCHAR(50)
);

-- Insert some file names into the table
INSERT INTO files (name) VALUES
('file1.txt'),
('file2.txt'),
('file10.txt'),
('file11.txt'),
('file20.txt'),
('file100.txt');

-- Use NATURAL_SORT_KEY() to sort file names in a natural order
SELECT name FROM files ORDER BY NATURAL_SORT_KEY(name);

The output is:

+-----------+
| name      |
+-----------+
| file1.txt |
| file2.txt |
| file10.txt|
| file11.txt|
| file20.txt|
| file100.txt|
+-----------+

The NATURAL_SORT_KEY() function returns a binary string that can be used to sort the file names in a natural order. The function pads the numerical parts with zeros to the default length of 10, and then concatenates the parts. For example, the natural sort key for file2.txt is file0000000002.txt, and the natural sort key for file100.txt is file0000000100.txt. The file names are then sorted by the natural sort keys, which results in the desired order.

Example 2: Using NATURAL_SORT_KEY() to sort version numbers

In this example, we use the NATURAL_SORT_KEY() function to sort version numbers in a natural order. We use the SELECT statement to display the result.

-- Create a table with version numbers
CREATE TABLE versions (
  number VARCHAR(50)
);

-- Insert some version numbers into the table
INSERT INTO versions (number) VALUES
('1.0'),
('1.1'),
('1.2'),
('1.10'),
('1.11'),
('2.0'),
('2.1'),
('2.10'),
('2.11'),
('10.0'),
('10.1'),
('10.10'),
('10.11');

-- Use NATURAL_SORT_KEY() to sort version numbers in a natural order
SELECT number FROM versions ORDER BY NATURAL_SORT_KEY(number);

The output is:

+-------+
| number|
+-------+
| 1.0   |
| 1.1   |
| 1.2   |
| 1.10  |
| 1.11  |
| 2.0   |
| 2.1   |
| 2.10  |
| 2.11  |
| 10.0  |
| 10.1  |
| 10.10 |
| 10.11 |
+-------+

The NATURAL_SORT_KEY() function returns a binary string that can be used to sort the version numbers in a natural order. The function pads the numerical parts with zeros to the default length of 10, and then concatenates the parts. For example, the natural sort key for 1.2 is 0000000001.0000000002, and the natural sort key for 10.10 is 0000000010.0000000010. The version numbers are then sorted by the natural sort keys, which results in the desired order.

Example 3: Using NATURAL_SORT_KEY() with a specified pad length

In this example, we use the NATURAL_SORT_KEY() function with a specified pad length to sort strings in a natural order. We use the SELECT statement to display the result.

-- Create a table with strings
CREATE TABLE strings (
  str VARCHAR(50)
);

-- Insert some strings into the table
INSERT INTO strings (str) VALUES
('a1'),
('a2'),
('a10'),
('a11'),
('a20'),
('a100'),
('b1'),
('b2'),
('b10'),
('b11'),
('b20'),
('b100');

-- Use NATURAL_SORT_KEY() with a pad length of 3 to sort strings in a natural order
SELECT str FROM strings ORDER BY NATURAL_SORT_KEY(str, 3);

The output is:

+------+
| str  |
+------+
| a1   |
| a2   |
| a10  |
| a11  |
| a20  |
| a100 |
| b1   |
| b2   |
| b10  |
| b11  |
| b20  |
| b100 |
+------+

The NATURAL_SORT_KEY() function returns a binary string that can be used to sort the strings in a natural order. The function pads the numerical parts with zeros to the specified length of 3, and then concatenates the parts. For example, the natural sort key for a2 is a002, and the natural sort key for b100 is b100. The strings are then sorted by the natural sort keys, which results in the desired order.

Conclusion

The NATURAL_SORT_KEY() function is a string function that returns a binary string that can be used to sort strings in a natural order. A natural order is a way of sorting strings that considers numerical parts as numbers, rather than as characters. The function works by splitting the input string into alphanumeric and non-alphanumeric parts, and then padding the numerical parts with zeros to the specified length. The function then concatenates the padded parts and returns the result. The function is useful for sorting file names, version numbers, or other strings that contain numerical parts. There are some other functions that are related to the `