Snippet Name: Sort comma separated string
Description: This User Defined Function sorts comma separated sub strings within a string.
Comment: (none)
Language: MYSQL
Highlight Mode: MYSQL
Last Modified: March 02nd, 2009
|
-- sort comma separated substrings with unoptimized bubble sort
DROP FUNCTION IF EXISTS sortString;
DELIMITER |
CREATE FUNCTION sortString(inString TEXT) RETURNS TEXT
BEGIN
DECLARE delim CHAR(1) DEFAULT ','; -- delimiter
DECLARE strings INT DEFAULT 0; -- number of substrings
DECLARE forward INT DEFAULT 1; -- index for traverse forward thru substrings
DECLARE backward INT; -- index for traverse backward thru substrings, position in calc. substrings
DECLARE remain TEXT; -- work area for calc. no of substrings
-- swap areas TEXT for string compare, INT for numeric compare
DECLARE swap1 TEXT; -- left substring to swap
DECLARE swap2 TEXT; -- right substring to swap
SET remain = inString;
SET backward = LOCATE(delim, remain);
WHILE backward != 0 DO
SET strings = strings + 1;
SET backward = LOCATE(delim, remain);
SET remain = SUBSTRING(remain, backward+1);
END WHILE;
IF strings < 2 THEN RETURN inString; END IF;
REPEAT
SET backward = strings;
REPEAT
SET swap1 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward-1),delim,-1);
SET swap2 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward),delim,-1);
IF swap1 > swap2 THEN
SET inString = TRIM(BOTH delim FROM CONCAT_WS(delim
,SUBSTRING_INDEX(inString,delim,backward-2)
,swap2,swap1
,SUBSTRING_INDEX(inString,delim,(backward-strings))));
END IF;
SET backward = backward - 1;
UNTIL backward < 2 END REPEAT;
SET forward = forward +1;
UNTIL forward + 1 > strings
END REPEAT;
RETURN inString;
END |
DELIMITER ;
-- example call:
SET @Xstr = "The,quick,brown,fox,jumped,over,the,lazy,dog";
SET @Ystr = "9,8,7,6,5,4,3,2,1";
SET @Zstr = "7,8,9,6,5,2,1,4,3";
SET @str = "3,2,3,4,2,3";
SELECT sortString(@str) AS s1,sortString(@Xstr) AS s2,sortString(@Ystr) AS s3,sortString(@Zstr) AS s4; |