MySQL：如何使用存储函数对字符串中的单词进行排序？ [英] MySQL: how to sort the words in a string using a stored function?

问题描述

我需要一个MySQL存储函数，用于对字符串中的单词（用空格分隔）进行排序并返回已排序的字符串。

例如：
The Quick Brown Fox - > Brown Fox Quick The

在PHP中，这将是一件容易的事情，但在MySQL存储函数中我不能使用数组，所以我现在有点卡住了。我可以创建一个临时表，但这看起来太过于夸张了......（并且很慢）。

任何想法都是？

解决方案

非常感谢Bob Vale！

我改变了他提出的一些指定自定义分隔符的函数

更新：

此功能并不完美。当字符串的开始或结尾与分隔符相同时，它会产生strang结果，并且当字符串中的行中有多个分隔符时，所以您必须确保这不是您的字符串中的情况。
$ b $ pre $ CREATE FUNCTION`SPLIT_SORT`（inString TEXT，inSeparator TEXT）
RETURNS文字CHARSET utf8
BEGIN
DECLARE字符串INT DEFAULT 0; - 子字符串的数量
DECLARE转发INT DEFAULT 1; - 遍历子串的前向索引
DECLARE后向INT; - 遍历反向子串的索引，计算中的位置。子字符串
DECLARE保持TEXT; - 计算工作区。子字符串否
- 交换区域TEXT用于字符串比较，INT用于数字比较
DECLARE swap1 TEXT; - 左子串交换
DECLARE swap2 TEXT; - 右侧子字符串交换

SET remaining = inString;
SET落后= LOCATE（inSeparator，保留）;
WHILE向后！= 0 DO
SET strings = strings + 1;
SET落后= LOCATE（inSeparator，保留）;
SET remaining = SUBSTRING（保留，向后+ 1）;
END WHILE;
IF字符串< 2然后返回inString;万一;
REPEAT
SET backward = strings;
REPEAT
SET swap1 = SUBSTRING_INDEX（SUBSTRING_INDEX（inString，inSeparator，backward-1），inSeparator，-1）;
SET swap2 = SUBSTRING_INDEX（SUBSTRING_INDEX（inString，inSeparator，backward），inSeparator，-1）;
IF swap1> swap2 THEN
SET inString = TRIM（BOTH inSeparator FROM CONCAT_WS（inSeparator
，SUBSTRING_INDEX（inString，inSeparator，backward-2）
，swap2，swap1
，SUBSTRING_INDEX（inString，inSeparator ，（向后字符串））））;
END IF;
SET落后=落后 - 1;
直到后退< 2 END REPEAT;
SET forward = forward +1;
UNTIL转发+ 1>字符串
END REPEAT;
RETURN inString;
END;


I need a MySQL stored function that sorts the words (separated by a space) in a string and return the sorted string.

For example : "The Quick Brown Fox" -> "Brown Fox Quick The"

In PHP this would be an easy task, but in MySQL stored functions I can't use arrays, so I'm a bit stuck now. I could create a temp table, but this seems so overkill... (and slow).

Any idea's ?

解决方案

Many thanks to Bob Vale !

I altered the function he suggested a little bit to specify a custom delimiter.

UPDATE:

This function is not perfect. It produces strangs results when the begin or end of the string is the same as the delimiter, and when there is more than 1 delimiter in a row in the string, so you'll have to make sure this is not the case in your strings.

CREATE FUNCTION `SPLIT_SORT`(inString TEXT, inSeparator TEXT)
 RETURNS text CHARSET utf8
BEGIN
  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(inSeparator, remain);
  WHILE backward != 0 DO
    SET strings = strings + 1;
    SET backward = LOCATE(inSeparator, 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,inSeparator,backward-1),inSeparator,-1);
      SET swap2 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,inSeparator,backward),inSeparator,-1);
      IF  swap1 > swap2 THEN
        SET inString = TRIM(BOTH inSeparator FROM CONCAT_WS(inSeparator
        ,SUBSTRING_INDEX(inString,inSeparator,backward-2)
        ,swap2,swap1
        ,SUBSTRING_INDEX(inString,inSeparator,(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;

这篇关于MySQL：如何使用存储函数对字符串中的单词进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！