การเรียง sql order แบบตัวเลขพร้อมตัวอักษร

จากบทความก่อนหน้านี้ที่เรียง sql แบบตัวเลขไปแล้ว ยังมีอีกกรณีหนึ่งที่ computer กับ มนุษย์เข้าใจไม่เหมือนกัน คือข้อมูลที่มีทั้งตัวอักษร (text) และตัวเลข (numeric) หรือเรียกว่า alphanumeric ในช่องที่เป็น varchar/text.

ตัวอย่างที่เรียกใช้แบบธรรมดา

SELECT * FROM `products` ORDER BY `product_name` ASC

แบบนี้ จะได้ผลลัพธ์ออกมาเป็น

หนังสือเล่ม 1 ...ไปจนถึง หนังสือเล่ม 19
หนังสือเล่ม 2 ...ไปจนถึง หนังสือเล่ม 29

วิธีแก้

วิธีที่ 1

ใช้ LENGTH เข้ามาช่วย

SELECT * FROM `products` ORDER BY LENGTH(`product_name`) ASC, `product_name` ASC

จะได้ผลลัพธ์ที่ถูกต้อง คือ

หนังสือเล่ม 1
หนังสือเล่ม 2
หนังสือเล่ม 3 ... ไปเรื่อยๆ

ทั้งนี้คำสั่ง LENGTH() จะนับจำนวนสตริงในคอลัมน์ที่ระบุ และจะมีผลลัพธ์ผิดพลาดทันทีถ้าหากคอลัมน์นั้นมีข้อมูลลักษณะนี้ เช่น Final Fantasy, Goofy. ตัวข้อมูล Goofy จะถูกนำขึ้นมาก่อน Final Fantasy เพราะจำนวนสตริงน้อยกว่า คำสั่งนี้จึงไม่เหมาะกับสถานการณ์ดังที่กล่าว.

อ้างอิงจาก https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

วิธีที่ 2

สร้าง function, routine ขึ้นมาช่วย

/**
 * Returns a string formatted for natural sorting. This function is very useful when having to sort alpha-numeric strings.
 *
 * @author Alexandre Potvin Latreille (plalx)
 * @param {nvarchar(4000)} instring The formatted string.
 * @param {int} numberLength The length each number should have (including padding). This should be the length of the longest number. Defaults to 10.
 * @param {char(50)} sameOrderChars A list of characters that should have the same order. Ex: '.-/'. Defaults to empty string.
 *
 * @return {nvarchar(4000)} A string for natural sorting.
 * Example of use: 
 * 
 *      SELECT Name FROM TableA ORDER BY Name
 *  TableA (unordered)              TableA (ordered)
 *  ------------                    ------------
 *  ID  Name                    ID  Name
 *  1.  A1.                 1.  A1-1.       
 *  2.  A1-1.                   2.  A1.
 *  3.  R1      -->         3.  R1
 *  4.  R11                 4.  R11
 *  5.  R2                  5.  R2
 *
 *  
 *  As we can see, humans would expect A1., A1-1., R1, R2, R11 but that's not how SQL is sorting it.
 *  We can use this function to fix this.
 *
 *      SELECT Name FROM TableA ORDER BY dbo.udf_NaturalSortFormat(Name, default, '.-')
 *  TableA (unordered)              TableA (ordered)
 *  ------------                    ------------
 *  ID  Name                    ID  Name
 *  1.  A1.                 1.  A1.     
 *  2.  A1-1.                   2.  A1-1.
 *  3.  R1      -->         3.  R1
 *  4.  R11                 4.  R2
 *  5.  R2                  5.  R11
 */
DROP FUNCTION IF EXISTS `udf_FirstNumberPos`;
DELIMITER ;;
CREATE FUNCTION `udf_FirstNumberPos` (`instring` varchar(4000)) 
RETURNS int
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
    DECLARE position int;
    DECLARE tmp_position int;
    SET position = 5000;
    SET tmp_position = LOCATE('0', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF; 
    SET tmp_position = LOCATE('1', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('2', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('3', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('4', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('5', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('6', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('7', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('8', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
    SET tmp_position = LOCATE('9', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;

    IF (position = 5000) THEN RETURN 0; END IF;
    RETURN position;
END
;;

DROP FUNCTION IF EXISTS `udf_NaturalSortFormat`;
DELIMITER ;;
CREATE FUNCTION `udf_NaturalSortFormat` (`instring` varchar(4000), `numberLength` int, `sameOrderChars` char(50)) 
RETURNS varchar(4000)
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
    DECLARE sortString varchar(4000);
    DECLARE numStartIndex int;
    DECLARE numEndIndex int;
    DECLARE padLength int;
    DECLARE totalPadLength int;
    DECLARE i int;
    DECLARE sameOrderCharsLen int;

    SET totalPadLength = 0;
    SET instring = TRIM(instring);
    SET sortString = instring;
    SET numStartIndex = udf_FirstNumberPos(instring);
    SET numEndIndex = 0;
    SET i = 1;
    SET sameOrderCharsLen = CHAR_LENGTH(sameOrderChars);

    WHILE (i <= sameOrderCharsLen) DO
        SET sortString = REPLACE(sortString, SUBSTRING(sameOrderChars, i, 1), ' ');
        SET i = i + 1;
    END WHILE;

    WHILE (numStartIndex <> 0) DO
        SET numStartIndex = numStartIndex + numEndIndex;
        SET numEndIndex = numStartIndex;

        WHILE (udf_FirstNumberPos(SUBSTRING(instring, numEndIndex, 1)) = 1) DO
            SET numEndIndex = numEndIndex + 1;
        END WHILE;

        SET numEndIndex = numEndIndex - 1;

        SET padLength = numberLength - (numEndIndex + 1 - numStartIndex);

        IF padLength < 0 THEN
            SET padLength = 0;
        END IF;

        SET sortString = INSERT(sortString, numStartIndex + totalPadLength, 0, REPEAT('0', padLength));

        SET totalPadLength = totalPadLength + padLength;
        SET numStartIndex = udf_FirstNumberPos(RIGHT(instring, CHAR_LENGTH(instring) - numEndIndex));
    END WHILE;

    RETURN sortString;
END
;;

โดยคำสั่งนี้จะเป็นการสร้าง function ขึ้นมา ให้เราสั่งเพียงครั้งเดียวแล้วฐานข้อมูลจะสร้าง function ไว้ใน routines ซึ่งการเรียกใช้ครั้งต่อไปก็เรียกใช้ได้เลย.
ตัวอย่างการเรียกใช้.

SELECT * FROM `products` ORDER BY udf_NaturalSortFormat(`product_name`, 10, ".") ASC

บางกรณีถ้าหากฐานข้อมูลมีข้อมูลจำนวนเยอะมากๆ เช่น หลักหมื่น, หลักแสนขึ้นไป การจัดเรียงโดยใช้ฟังก์ชั่นแบบนี้อาจทำให้ใช้ทรัพยากรเครื่อง server มากและอาจจะทำให้ทำงานล่าช้า. ดังนั้นการแก้ปัญหาจึงอาจจะใช้วิธีการเพิ่มคอลัมน์อีก 1 สำหรับการจัดเรียงโดยเฉพาะ เช่น คอลัมน์ชื่อ product_name_natsort สำหรับเก็บข้อมูลจากคอลัมน์ product_name ที่ผ่านการสร้างข้อมูลให้เหมาะสมสำหรับจัดเรียงแล้ว. และเมื่อมีการเพิ่มหรือปรับปรุงข้อมูล ก็สั่งให้มีการ build ข้อมูลนี้ด้วยฟังก์ชั่นดังกล่าว ตัวอย่างเช่น

UPDATE `products` SET `product_name` = :product_name, `product_name_natsort` = udf_NaturalSortFormat(:product_name, 10, ".") WHERE `id` = :id

จากนั้นการเรียกใช้เรียงลำดับก็ใช้คำสั่งต่อไปนี้แทน.

SELECT * FROM `products` ORDER BY `product_name_natsort` ASC

ที่มา: https://stackoverflow.com/a/12257917/128761

วิธีที่ 3

วิธีนี้ใช้ได้กับ MariaDB รุ่น 10.7.0 ขึ้นไปเท่านั้น คือใช้ฟังก์ชั่น NATURAL_SORT_KEY().

ตัวอย่าง

SELECT * FROM `products` ORDER BY NATURAL_SORT_KEY(`product_name`) ASC

ซึ่งถ้าหาก collation ในคอลัมน์นี้เป็นภาษาไทย เช่น utf8mb4_thai_520_w2 ก็จะทำงานเรียงภาษาไทยได้ถูกต้องง่ายดายอีกด้วย.

การเรียงภาษาไทย

สำหรับผู้ที่ต้องการเรียงลำดับตัวอักษรภาษาไทยที่ถูกต้องตามหลัก เช่น คำว่า ไก่ ควรมาก่อน ขวด เป็นต้น สามารถใช้ collation utf8mb4_thai_520_w2 ได้เลย. แต่ถ้าต้องการใช้ collation อื่น เช่น utf8mb4_unicode_ci เพื่อรักษาคุณสมบัติในการเก็บอักขระตัวอักษรให้กว้างขวาง ก็ยังสามารถทำได้ คือ สามารถใช้คำสั่ง CONVERT(`product_name_natsort` USING tis620) เข้ามาช่วยได้ในขั้นตอนการจัดเรียง.

ที่มา: http://web-programming-bookmark.blogspot.com/2013/10/php-sorting-thai-characters.html