Shuffle a string with mysql/sql

问题:

I was wondering, if there is some way to shuffle the letters of a string in mysql/sql, i.e. something like the pseudocode: SELECT SHUFFLE('abcdef')?

Couldn't find any from http://dev.mysql.com/doc/refman/5.0/en/string-functions.html and searching for it just seems to find solutions for shuffling results, not a string.

回答1:

Here you go:

DELIMITER //

DROP FUNCTION IF EXISTS shuffle;

CREATE FUNCTION shuffle(
    v_chars TEXT
)
RETURNS TEXT
NOT DETERMINISTIC -- multiple RAND()'s
NO SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
    DECLARE v_retval TEXT DEFAULT '';
    DECLARE u_pos    INT UNSIGNED;
    DECLARE u        INT UNSIGNED;

    SET u = LENGTH(v_chars);
    WHILE u > 0
    DO
      SET u_pos = 1 + FLOOR(RAND() * u);
      SET v_retval = CONCAT(v_retval, MID(v_chars, u_pos, 1));
      SET v_chars = CONCAT(LEFT(v_chars, u_pos - 1), MID(v_chars, u_pos + 1, u));
      SET u = u - 1;
    END WHILE;

    RETURN v_retval;
END;
//

DELIMITER ;

SELECT shuffle('abcdef');

See sqlfiddle.com for the output.

回答2:

Edit: this solution is for Microsoft SQL Server.

As it's not allowed to use RAND() in user defined function, we create a view to use it later in our shuffle function:

CREATE VIEW randomView
AS
SELECT RAND() randomResult
GO

The actual shuffle function is as following:

CREATE FUNCTION shuffle(@string NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) AS 
BEGIN

    DECLARE @pos INT 
    DECLARE @char CHAR(1)
    DECLARE @shuffeld NVARCHAR(MAX)
    DECLARE @random DECIMAL(18,18) 

    WHILE LEN(@string) > 0
        BEGIN
            SELECT @random = randomResult FROM randomView
            SET @pos = (CONVERT(INT, @random*1000000) % LEN(@string)) + 1
            SET @char = SUBSTRING(@string, @pos, 1)
            SET @shuffeld = CONCAT(@shuffeld, @char)

            SET @string = CONCAT(SUBSTRING(@string, 1, @pos-1), SUBSTRING(@string, @pos+1, LEN(@string)))
        END

    RETURN @shuffeld

END 

Calling the function

DECLARE @string NVARCHAR(MAX) = 'abcdefghijklmnonpqrstuvwxyz0123456789!"§$%&/()='
SELECT dbo.shuffle(@string)

回答3:

There is nothing in standard SQL - your best bet is probably to write a user defined function