MySQL select order by contains num

Question

This is my table:

--------------
    Names
--------------
   "Ben 52"
   "Dan 236"
   "Carter 73"
   "Harry"
   "Peter 53"
   "Connor 27"
   "Morgan"
   "Richard 675"

I want to create a query that puts the columns without numbers at the end of the list. This is basically the idea of what I'm trying to create:

SELECT names FROM table1 ORDER BY ContainsNum(names) DESC

This is the result I should get:

--------------
    Names
--------------
   "Ben 52"
   "Dan 236"
   "Carter 73"
   "Peter 53"
   "Connor 27"
   "Richard 675"
   "Harry"
   "Morgan"

Please comment if I wasn't clear enough. I haven't see anyone ask this question before.

Thank you!

@petethepagan-gerbil I've tried to use cases and few other statments but none of them were even close to work. — morha13, Aug 04 '15 at 14:54
@TabAlleman I have been searching through other tutorials, but I couldn't find anything that does anything like what I'm trying to do — morha13, Aug 04 '15 at 14:54
possible duplicate of [How would I determine if a varchar field in SQL contains any numeric characters?](http://stackoverflow.com/questions/4324912/how-would-i-determine-if-a-varchar-field-in-sql-contains-any-numeric-characters) — Tab Alleman, Aug 04 '15 at 14:57
@TabAlleman It's not duplicated.. Please check the differences before you tag it as duplicated — morha13, Aug 04 '15 at 15:04
@TabAlleman I wanted to determine which columns contains a number, and then order the columns so the columns with numbers will be at the top and the ones which doesn't contains number will be at the bottom. The other question only wants to determine which columns contains a number. **Can you spot the difference now?**. As far as I see all the other people who answered this question could spot it. — morha13, Aug 04 '15 at 17:57
On SO, a duplicate question doesn't have to be an exact duplicate. The answer to the duplicate question can be used to answer your question. The only difference is that you want to apply an ORDER BY to the answer. The answer you accepted is the same as the answer accepted in the duplicate, with the addition of using it to ORDER BY. — Tab Alleman, Aug 04 '15 at 18:03
@TabAlleman Does that mean I'm not allowed to post any question that a small part of the answer will be similar to another question's answer? Is that means that if someone asked how to echo text in php, no one else is allowed to ask anything that uses the echo function? I saw the question you sent before I posted that question and yet I didn't success to solve my problem. Why is this question duplicated? **I see a big difference between asking for a function the determines a number then asking for ordering the ones without numbers at the bottom.** — morha13, Aug 04 '15 at 18:12

Gordon Linoff · Accepted Answer · 2015-08-04T14:58:42.740

4

Just use order by. Most databases support some form of regular expression, something like this:

order by (case when names regexp '.*[0-9].*' then 0 else 1 end),
         names

The exact syntax varies depending on the database.

Note: it works with just names regexp '[0-9]'. I added the wildcards because I think it makes the pattern more intuitive.

edited Aug 04 '15 at 14:58

answered Aug 04 '15 at 14:55

Gordon Linoff

1,242,037
58
646
786

Thank you it worked! I can accept it only in 8 minutes so I will accept it soon :) – morha13 Aug 04 '15 at 14:58

pete the pagan-gerbil · Answer 2 · 2015-08-04T15:08:40.410

0

SELECT names FROM table1 ORDER BY SUBSTRING(names, LENGTH(names)-2, 1) DESC

Everything which ends with a number will be below anything which ends with a character.

edited Aug 04 '15 at 15:08

answered Aug 04 '15 at 14:57

pete the pagan-gerbil

3,136
2
28
49

It says LEN function doesn't exist. As you can see the other guy solved the problem so thank you anyways for trying helping me! – morha13 Aug 04 '15 at 15:01
1

You query will not work for numbers with more than two digits. – Tony Aug 04 '15 at 16:16
Why not? The numbers in the original question aren't in order, the only requirement was that 'records with numbers come after records without'. – pete the pagan-gerbil Aug 06 '15 at 07:18

score 0 · Answer 3 · answered Aug 04 '15 at 15:16

0

SELECT names, case when REGEXP_LIKE(names, '[[:digit:]]') then '1' else '2' end ordering_col
FROM table1
order by 2;

answered Aug 04 '15 at 15:16

Neeraj B.

455
1
6
12

2

There's no need to put the sort order information in the result set. See [Gordon Linoff's answer](http://stackoverflow.com/a/31812657/243925). – Tony Aug 04 '15 at 15:21

MySQL select order by contains num

3 Answers3