Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 mySQL: Sort comma separated string Jump to:  
Category: >> mySQL >> Sort comma separated string Bookmark and Share

<< lastnext >>

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;


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 151 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?