001./**
002. * Returns a string formatted for natural sorting. This function is very useful when having to sort alpha-numeric strings.
003. *
004. * @author Alexandre Potvin Latreille (plalx)
005. * @param {nvarchar(4000)} instring The formatted string.
006. * @param {int} numberLength The length each number should have (including padding). This should be the length of the longest number. Defaults to 10.
007. * @param {char(50)} sameOrderChars A list of characters that should have the same order. Ex: '.-/'. Defaults to empty string.
008. *
009. * @return {nvarchar(4000)} A string for natural sorting.
010. * Example of use:
011. *
012. * SELECT Name FROM TableA ORDER BY Name
013. * TableA (unordered) TableA (ordered)
014. *
015. * ID Name ID Name
016. * 1. A1. 1. A1-1.
017. * 2. A1-1. 2. A1.
018. * 3. R1
019. * 4. R11 4. R11
020. * 5. R2 5. R2
021. *
022. *
023. * As we can see, humans would expect A1., A1-1., R1, R2, R11 but that's not how SQL is sorting it.
024. * We can use this function to fix this.
025. *
026. * SELECT Name FROM TableA ORDER BY dbo.udf_NaturalSortFormat(Name, default, '.-')
027. * TableA (unordered) TableA (ordered)
028. * ------------ ------------
029. * ID Name ID Name
030. * 1. A1. 1. A1.
031. * 2. A1-1. 2. A1-1.
032. * 3. R1 --> 3. R1
033. * 4. R11 4. R2
034. * 5. R2 5. R11
035. */
036.DROP FUNCTION IF EXISTS `udf_FirstNumberPos`;
037.DELIMITER ;;
038.CREATE FUNCTION `udf_FirstNumberPos` (`instring` varchar(4000))
039.RETURNS int
040.LANGUAGE SQL
041.DETERMINISTIC
042.NO SQL
043.SQL SECURITY INVOKER
044.BEGIN
045. DECLARE position int;
046. DECLARE tmp_position int;
047. SET position = 5000;
048. SET tmp_position = LOCATE('0', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
049. SET tmp_position = LOCATE('1', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
050. SET tmp_position = LOCATE('2', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
051. SET tmp_position = LOCATE('3', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
052. SET tmp_position = LOCATE('4', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
053. SET tmp_position = LOCATE('5', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
054. SET tmp_position = LOCATE('6', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
055. SET tmp_position = LOCATE('7', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
056. SET tmp_position = LOCATE('8', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
057. SET tmp_position = LOCATE('9', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
058.
059. IF (position = 5000) THEN RETURN 0; END IF;
060. RETURN position;
061.END
062.;;
063.
064.DROP FUNCTION IF EXISTS `udf_NaturalSortFormat`;
065.DELIMITER ;;
066.CREATE FUNCTION `udf_NaturalSortFormat` (`instring` varchar(4000), `numberLength` int, `sameOrderChars` char(50))
067.RETURNS varchar(4000)
068.LANGUAGE SQL
069.DETERMINISTIC
070.NO SQL
071.SQL SECURITY INVOKER
072.BEGIN
073. DECLARE sortString varchar(4000);
074. DECLARE numStartIndex int;
075. DECLARE numEndIndex int;
076. DECLARE padLength int;
077. DECLARE totalPadLength int;
078. DECLARE i int;
079. DECLARE sameOrderCharsLen int;
080.
081. SET totalPadLength = 0;
082. SET instring = TRIM(instring);
083. SET sortString = instring;
084. SET numStartIndex = udf_FirstNumberPos(instring);
085. SET numEndIndex = 0;
086. SET i = 1;
087. SET sameOrderCharsLen = CHAR_LENGTH(sameOrderChars);
088.
089. WHILE (i <= sameOrderCharsLen) DO
090. SET sortString = REPLACE(sortString, SUBSTRING(sameOrderChars, i, 1), ' ');
091. SET i = i + 1;
092. END WHILE;
093.
094. WHILE (numStartIndex <> 0) DO
095. SET numStartIndex = numStartIndex + numEndIndex;
096. SET numEndIndex = numStartIndex;
097.
098. WHILE (udf_FirstNumberPos(SUBSTRING(instring, numEndIndex, 1)) = 1) DO
099. SET numEndIndex = numEndIndex + 1;
100. END WHILE;
101.
102. SET numEndIndex = numEndIndex - 1;
103.
104. SET padLength = numberLength - (numEndIndex + 1 - numStartIndex);
105.
106. IF padLength < 0 THEN
107. SET padLength = 0;
108. END IF;
109.
110. SET sortString = INSERT(sortString, numStartIndex + totalPadLength, 0, REPEAT('0', padLength));
111.
112. SET totalPadLength = totalPadLength + padLength;
113. SET numStartIndex = udf_FirstNumberPos(RIGHT(instring, CHAR_LENGTH(instring) - numEndIndex));
114. END WHILE;
115.
116. RETURN sortString;
117.END
118.;;
