CODEWITHSUNDEEP
mysqlstring
Joe Phillips
Joe Phillips

Reputation: 51150

MySQL function to order string alphabetically

I'm wondering if there is a way I can pass in a string (varchar) to a (custom) function in MySQL and then have it output the string ordered alphabetically. I am unsure of how you would actually reorder the string.

IN : dbca
OUT: abcd

Upvotes: 2

Views: 532

Answers (4)

Joe Phillips
Joe Phillips

Reputation: 51150

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);
}

loop 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();
}

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562398

One could probably define a stored function that could do this, but it would be pretty awkward and inefficient. This doesn't taking advantage of the strengths of SQL.

However, it would be simple in many dynamic programming languages:

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

Upvotes: 2

VoteyDisciple
VoteyDisciple

Reputation: 37803

You won't be able to do that directly through SQL; you'll have to do a SELECT and UPDATE from a programming language.

Upvotes: 2

Zed
Zed

Reputation: 57658

I'd be surprised if there was a such function. Never mind, you can implement your own functions.

Upvotes: 2

Related Questions