How does SQL define "alphabetical order", precisely?

Question

Most algorithms for alphabetical order give similar results, but there are a few edge cases, three are mentioned in this article.

For example:

• Newspapers
• News photography
• Newsprint

Is that correct order? Or does the space character come before "p"? Or after "p"?

Also:

• Article 19
• Article 2
• Article 20
• Article 21

Is that correct order? What if there's no space, like "Article2"? Where can I find the exact definition of alphabetical order, that will clarify this and all other edge cases?

Writing "Article 02" should put it in the correct place, but is there a character other than "0" that will work while being easier for customers to read? Like a punctuation mark or something? (Assuming I'm working with someone else's database and I only have data entry permissions.)

This question is covered in the PHP case fairly well by considering the differences between sort() and natsort(). (Click to see the relevant documentation.)

Answer 1

The order is defined in the "collation" of the VARCHAR column. There's a default collation when this is not specified, but you can also a different one as needed.

XAMPP uses MariaDB as a database. The MariaDB - ORDER BY Clause reads:

...When string values are compared, they are compared as if by the STRCMP function. STRCMP ignores trailing whitespace and may normalize characters and ignore case, depending on the collation in use...

Answer 2

This is more an end of the story than an answer, but recreating the titles was far too ugly a solution, so I had to take the time to edit the code.

So instead of:

while ($row = mysqli_fetch_assoc($result)){
    //do stuff
}

I had to

while ($row = mysqli_fetch_assoc($result)){
    $data[$row['product']] = $row;
    $title[$row['product']] = $row['title'];
}
array_multisort($title, SORT_ASC, SORT_NATURAL, $data);
foreach($data as $row){
    //do stuff
}

Note that the //do stuff section didn't require any editing whatsoever. It worked exactly as it did before.

I suspect that will help at least one person browsing this question.