MySQL IF begins with a number do X, else do Y

Question

Possible Duplicate:
MySQL - If It Starts With A Number Or Special Character
Select values that begin with a number

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.

Something like this IF(title begins with number, title + 0, title)

Not sure how to go about this.

possible duplicates: [Select values that begin with a number](http://stackoverflow.com/questions/4511945); [MySQL - If It Starts With A Number Or Special Character](http://stackoverflow.com/questions/1214035) — mellamokb, Jan 21 '13 at 20:57

spencer7593 · Accepted Answer · 2013-01-21T21:52:27.473

ORDER BY IF(title REGEXP '^[0-9]',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.

(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?

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

score 2 · Answer 2 · answered Jan 21 '13 at 20:59

2

You could force the title to be interpreted as an integer and test that it's >= 1:

ORDER BY IF(title+0 >= 1, title+0, title)

But I have to comment that it sounds like you're using one column for two different purposes, which is a no-no in relational database design.

Also it's going to be really slow, since the ORDER BY can't benefit from an index.

answered Jan 21 '13 at 20:59

Bill Karwin

538,548
86
673
828

Well since I'm only grabbing 50 results at a time I'm not too worried about the speed. I choose to think that the lack of natural sort order is the real issue at hand. – kylex Jan 22 '13 at 00:13

score 0 · Answer 3 · answered Jan 21 '13 at 20:58

0

if title LIKE 'number%' then
    CONCAT(title, '0');
end if;

answered Jan 21 '13 at 20:58

Ms01

4,420
9
48
80

score 0 · Answer 4 · answered Jan 21 '13 at 20:59

0

if (left(title,1) regexp '[0-9]', title+0, title)

or

if (title regexp '^[0-9]', title+0, title)

answered Jan 21 '13 at 20:59

ysth

96,171
6
121
214

MySQL IF begins with a number do X, else do Y

4 Answers4

Linked