Code

Coding, Programming & Algorithms, Tips, Tweaks & Hacks
Search

Sorting a String in SQL

Even though stored procedures are really slow, sometimes they come in handy.
SQL
DROP FUNCTION IF EXISTS `SORTEDSTRING`;
DELIMITER ///
CREATE FUNCTION SORTEDSTRING(s VARCHAR(1000)) RETURNS VARCHAR(1000) DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT;
    DECLARE si CHAR(1);
    DECLARE sj CHAR(1);
    DECLARE ss VARCHAR(1000); -- Sorted String

    SET ss = s;
    SET @length = LENGTH(s);

    WHILE i < @length DO
        SET j = i + 1;
        WHILE j <= @length DO
            SET si = SUBSTRING(ss, i, 1);
            SET sj = SUBSTRING(ss, j, 1);
            IF si > sj THEN
                SET ss = INSERT(ss, i, 1, sj);
                SET ss = INSERT(ss, j, 1, si);
            END IF;
            SET j = j + 1;
        END WHILE;
        SET i = i + 1;
    END WHILE;

    RETURN ss;
END
///
DELIMITER ;

mysql> SELECT SORTEDSTRING("elephant");
+--------------------------+
| SORTEDSTRING("elephant") |
+--------------------------+
| aeehlnpt                 |
+--------------------------+
1 row in set (0.00 sec)

mysql>
MySQL 5.0
Vanakkam !

0 comments: