Shuffle a string with mysql/sql

Question

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.

score 15 · Accepted Answer · edited Sep 08 '20 at 08:53

15

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.

Tested successfully with mariadb 10.1 (mysql 5.6 equivalent)

edited Sep 08 '20 at 08:53

Serg

2,346
3
29
38

answered Aug 08 '12 at 08:13

Ross Smith II

11,799
1
38
43

Uh, wow, didn't know to expect this. What I had in mind was a "clever" use of some sort of string operation / rand selects to manipulate the string. This goes beyond my understanding, but you certainly have my thanks for this effort! I'll see what other answers people come up with. Any pointer as to how I'd use a "function declaration" like this? Is it possible to run this as mysql query and then use the function? – kontur Aug 08 '12 at 08:46
Can I add a function like that via phpmyadmin, by executing it from the sql tab? While this looks like a great solution, I am constrained to mysql4 here, so this solution seems out of the window :/ – kontur Aug 09 '12 at 06:20
You can run this from phpmyadmin, but unfortunately, you will need MySQL 5.0 or greater. – Ross Smith II Aug 09 '12 at 06:39
Is there some other configuration that is needed, or version? Got mysql 5.5.9, but I get syntax errors when I try run your sql. – kontur Aug 09 '12 at 06:59
See http://www.sqlfiddle.com/#!2/14e41/4 . What is the exact error message you are getting? – Ross Smith II Aug 09 '12 at 07:08
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 10. I am using a local installation of MAMP on Mac OS X – kontur Aug 09 '12 at 07:31
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE FUNCTION shuffle( v_chars TEXT ) RETURNS TEXT NOT DETERMINISTIC -' at line 3 – Mikhail Batcer Oct 20 '17 at 13:02
it doesn't work with Arabic strings. how do I shuffle Arabic string of a field? – Far Nov 22 '20 at 06:40

sinan.petrus · Answer 2 · 2015-01-05T07:35:39.880

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)

You should probably add why/in which way your answer is different and/or better than the accepted answer — Bowdzone, Jan 02 '15 at 14:34
The accepted awnser uses RAND() in UDF, this doesn't work in MSSQL — sinan.petrus, Jan 05 '15 at 07:30

score 0 · Answer 3 · answered Aug 08 '12 at 07:59

0

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

answered Aug 08 '12 at 07:59

podiluska

50,950
7
98
104

Shuffle a string with mysql/sql

3 Answers3

Linked