MySQL IF 以数字开头，做 X，否则做 Y [英] MySQL IF begins with a number do X, else do Y

问题描述

可能的重复:
MySQL - 如果它以数字或特殊字符开头
选择以数字开头的值

我正在尝试创建一个 ORDER BY 语句，该语句检查列是否以数字开头，如果是，则应将 + 0 添加到该列.如果没有，那就没有.

I'm trying to create an ORDER BY statement that checks to see if the column begins with a number, if it does it should add + 0 to the column. If not, it does not.

类似这样的 IF(title 以数字开头，title + 0，title)

不知道该怎么做.

推荐答案

ORDER BY IF(title REGEXP '^[0-9]',title+0,title)

但是，该表达式将返回一个数值；并且由于title+0"本质上等于title"(就数值比较而言)，我认为这不会按照您的意愿行事.

But, that expression is going to return a numeric value; and since "title+0" is essentially equal to "title" (in terms of a numeric value comparison), I don't think that's going to do what you want it to.

(+0"操作将在字符串值的前导数字部分添加零，并返回一个数字值；因此整个表达式将被评估为一个数字值.这意味着裸标题"将也是一个数值，如果没有前导数字则返回0.

(The "+0" operation will add zero to the leading numeric portion of the string value, and return a numeric value; so the entire expression will be evaluated as a numeric value. Which means that the bare "title" will also be a numeric value, which will be returned as 0 if there is no leading numeric.

所以，这基本上等同于:

So, that is essentially equivalent to:

ORDER BY title+0

您实际上希望以什么顺序返回标题值?您是否希望按前导数字和标题字符串的顺序返回行?

In what order do you actually want the title values returned in? Do you want the rows returned in order by the leading numeric, and then by the title string?

我认为您想在没有前导数字时按 title 的字符串值排序；否则，通过前导数字的数值，然后是标题的字符串值，这样的事情可能会让你更接近你想要的:

I'm thinking you want to order by the string value of title when there isn't a leading numeric digit; otherwise, by the numeric value of leading numeric digits, and then the string value of title, so something like this might get you closer to what you want:

ORDER BY title REGEXP '^[0-9]', title+0, title

这篇关于MySQL IF 以数字开头，做 X，否则做 Y的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！