Sorting Alpha Numeric Data in MySQL

Methods to Sort Alpha- numeric Data in MySQL Abdul Rahman Sherzad Lecturer at Computer Science faculty Herat University, Afghanistan

ORDER BY Keyword • In SQL, the ORDER BY keyword is used to sort the result-set in ascending (ASC) or descending (DESC) order by some specified column/columns. • It works great for most of the cases. • However, for alphanumeric data, it may not return the result-set that you will be expecting. • This presentation explains how this can be addressed using different techniques. 2

Scenario I: Table Structure and Test Data Table Structure CREATE TABLE warnings ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, due VARCHAR(20) ); Test Data INSERT INTO warnings (name, due) VALUES ('Aaaaa', '10 days'), ('Baaaa', '1 days'), ('Ccccc', '2 days'), ('Ddddd', '12 days'), ('Eeeee', '20 days'), ('Fffff', '2 days'), ('Ggggg', '5 days'), ('Hhhhh', '3 days'); 3

Scenario I: The Problem • Assume there is a table named 'warnings' with the following 'due' and 'name' columns. • The data for 'due' column is alphanumeric. • A report is needed to display the data sorted by the 'due' column. But, the result of the following query is not as it is expected: SELECT due, name FROM warnings ORDER BY due ASC; 4

Solution #1: Identity Elements 5 • The number '0' in addition, and '1' in multiplication are identity elements. An identity element is a number that combines with other elements in a mathematical equation but does not change them. SELECT due, name FROM warnings ORDER BY due + 0 ASC; OR the following SELECT due, name FROM warnings ORDER BY due * 1 ASC;

Solution #2: CAST() function 6 • The CAST() function converts a value from one datatype to another datatype. • Using cast() function is another method to address the mentioned problem as follow: SELECT due, name FROM warnings ORDER BY CAST(due AS SIGNED) ASC;

Solution #3: Natural Sorting 7 • It is simple enough to accomplish natural sorting in MySQL: • First sort by length of the column, • Then sort by the original column value. SELECT due, name FROM warnings ORDER BY LENGTH(due) ASC, due ASC; • NOTE: The ASC keyword can be omitted, as it is the DEFAULT.

This is not end of the story! • The above Solution #1 and Solution #2 works only with alpha-numeric data starts with numbers. • The Solution #1 and Solution #2 do not work with alpha-numeric data ends with numbers! 8

Scenario II: Table Structure and Test Data Table Structure CREATE TABLE tests ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, score INT ); Test Data INSERT INTO tests (name, score) VALUES ('Test 1', 10), ('Test 10', 5), ('Test 3', 10), ('Test 2', 4), ('Test 15', 5), ('Test 18', 10), ('Test 20', 5), ('Test 9', 10); 9

Scenario II: The Problem 10 • Assume there is a table named 'tests' with the following two columns 'name' and 'score'. • The data in the column 'name' are alpha-numeric • but the numbers are at the end of the string. • With such a structure, the above-mentioned Solution #1 and Solution #2 do not work as illustrated on next slide 

Solution #1 and Solution #2: Issue 11 • The following queries do not sort the result-sets as it is expected: • Solution #1: SELECT name, score FROM tests ORDER BY name + 0 ASC; • Solution #2: SELECT name, score FROM tests ORDER BY CAST(name AS UNSIGNED);

Solution #3: Natural Sorting 12 • The natural sorting works properly with alpha-numeric data whether the numbers are at the beginning, or at the end of the string, as illustrated on this slide. SELECT name, score FROM tests ORDER BY LENGTH(name) ASC, name ASC;

What about the following Scenario? • What about mixture of data (a very rare case) • alpha-numeric data with numbers at the beginning of the string • alpha-numeric data with numbers at the end of the string • Only numeric data • Only alphabetic data 13

Scenario III: Table Structure and Test Data Table Structure CREATE TABLE tests ( test VARCHAR(20) NOT NULL ); Test Data INSERT INTO tests (test) VALUES ('A1'), ('A10'), ('A2’), ('1 day'), ('10 day'), ('2 day’), ('10'), ('1'), ('2’), ('Sherzad’), ('Abdul Rahman'); 14

Scenario III: ORDER BY Keyword 15 SELECT test FROM tests ORDER BY test ASC; NOTE: The ASC keyword can be omitted, as it is the DEFAULT.

Scenario III: Identity Elements and CAST() function 16 • Casting using Identity Elements SELECT test FROM tests ORDER BY test + 0 ASC; • CAST() function SELECT test FROM tests ORDER BY CAST(test AS UNSIGNED) ASC; NOTE: The ASC keyword can be omitted, as it is the DEFAULT.

Scenario III: Identity Elements and CAST() function 17 • To sort the data based on the numeric values, simply use the following queries: SELECT test FROM tests ORDER BY test + 0 ASC, test ASC; • OR SELECT test FROM tests ORDER BY CAST(test AS UNSIGNED) ASC, test ASC; NOTE: The ASC keyword can be omitted, as it is the DEFAULT.

Scenario III: Natural Sorting 18 SELECT test FROM tests ORDER BY LENGTH(test) ASC, test ASC; NOTE: The ASC keyword can be omitted, as it is the DEFAULT.

Summary In most cases including alpha-numeric data with numbers either at the beginning or at the end of the string Natural Sorting method works pretty well. •First sort by length of the column, •Then sort by the original column value. In case there are different variations of data in same column (which is very rare), different methods can be picked e.g. • The data can be sorted based on their numeric values as illustrated on slide 17, • The data can be sorted using Natural Sorting method, • Or combination of other methods 19 1 2

Sorting Alpha Numeric Data in MySQL

In this document

More Related Content

Similar to Sorting Alpha Numeric Data in MySQL

More from Abdul Rahman Sherzad

Recently uploaded

Sorting Alpha Numeric Data in MySQL