Clever Geek Handbook

MySQL function to order string alphabetically

I'm wondering if there is a way to pass a string (varchar) to a (user-defined) function in MySQL and then output the string, sorted alphabetically. I'm not sure how you actually change the order of the lines.

IN : dbca
OUT: abcd
+2


source to share


4 answers


It would be possible to define a stored function that could do this, but that would be rather awkward and inefficient. It doesn't take advantage of the strengths of SQL.

However, this would be easy in many dynamic programming languages:



<?php
$in = "dbca";
$out = str_split($in);
sort($out);
print_r($out);
+2


source


I would be surprised if there was such a function. It doesn't matter, you can implement your own functions .



+2


source


You cannot do this directly through SQL; you will need to do SELECT

it UPDATE

from the programming language as well.

+2


source


I decided to take Bill's example, and this is what I came up with:

function orderAlpha($letters)
{
    $mySortedArray = str_split($letters);
    sort($mySortedArray);

    return implode($mySortedArray);
}

until all records are updated:

$stmt = new PDOStatement(); 
$stmt = $dbh->prepare("SELECT word FROM words WHERE ordered IS NULL LIMIT 1");
$stmt->execute();

if ($stmt->rowCount() > 0) {
    $result = $stmt->fetchAll();
    $word = $result[0]['word'];
    $orderedWord = orderAlpha($word);
    $stmt2 = new PDOStatement();
    $stmt2 = $dbh->prepare("UPDATE words SET ordered = :orderedWord WHERE word = :word");

    $stmt2->bindParam(':orderedWord', $orderedWord, PDO::PARAM_STR);
    $stmt2->bindParam(':word', $word, PDO::PARAM_STR);
    $stmt2->execute();
}
0


source






More articles:


All Articles