mysql order string as number

Question

I have searched for days for a way to sort a string as number in mysql.

My rows look like this:

order_column

1.1.2
1.1.100
1.1.1

Ordering ascending by this column in mysql will produce:

1.1.1
1.1.100
1.1.2

I am interested in getting the following result:

1.1.1
1.1.2
1.1.100

Is there a way to produce this result using SQL?

Other possible values of my column:

28.1999.1.1
1
1.1.154.20
100.1.1.1.1.15

Thanks, i appreciate everybody's time.

EDIT: Fast Relational method of storing tree data (for instance threaded comments on articles) Take a look at the first answer, the one by Ayman Hourieh. I am trying to get that solution working. My website has no more than 10k comments per article, so i found a work-around like this:

000001
000002
000002.000001
000002.000001.000001
000002.000002
000002.000003
000003

Basically, putting zeros before my comment counter so that string comparison doesn't fail. But this will only work for 99999 comments. I can add more zeros, lets say 10 zeros, and that will work for 999999999 comments, but i was hoping for a more elegant solution.

score 4 · Answer 1 · answered Dec 18 '12 at 13:53

4

If string is not too long you can do

ORDER BY cast(replace(your_column_name,'.','') as unsigned);

answered Dec 18 '12 at 13:53

a1ex07

36,826
12
90
103

nice...But if column values are like 1.2.1.1 and 1.1.1.100 then order will be wrong i think... – Mudassir Hasan Dec 18 '12 at 13:56
@mhasan : the query gives desired output as stated in question :`1.1.1 1.1.2,1.1.100` .And I'm not sure what is the 'correct' order of ` 1.2.1.1, 1.1.1.100`. With no extra details given, I assume treating string as integer after removing ".".... – a1ex07 Dec 18 '12 at 14:01
What if there will be two values: '28.1999.1.1' and '2819.99.1.1'? – Devart Dec 18 '12 at 14:21
@Devart : after removing "." they will be the same, 28199911 and 28199911. Again, with no requirements given, I have no idea what is desired behaviour. – a1ex07 Dec 18 '12 at 15:24
I think desired behaviour is 28 < 2819, so '28.1999.1.1' < '2819.99.1.1'. But, who knows? – Devart Dec 18 '12 at 15:46
I have edited the question and added more requirements. Maybe you can get a clearer picture now. Thanks anyways. – razvansg Dec 19 '12 at 08:51

score 2 · Answer 2 · answered Dec 18 '12 at 15:04

You won't be able to do this efficiently since this will miss all indexes, but if it's a once-off, this will work. Sadly it looks horrible due to MySQL's inability (that I know of) to replace more than one character at a time.

SELECT value FROM TEST ORDER BY 
   REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(
                REPLACE(
                  REPLACE(value, 
                    '1', '0'),
                  '2', '0'), 
                '3', '0'), 
              '4', '0'), 
            '5', '0'), 
          '6', '0'), 
        '7', '0'), 
      '8', '0'), 
    '9', '0'),value;

It will basically replace all digits with a 0 and order by that first. Since . orders before 0, it will correctly order by digit groups. When that's done, just order by value since any values with the same digit groups should order in the correct order.

To make this more efficient, you could store the replaced value as a column in the database so you could index it.

SQLfiddle demo here.

score 1 · Answer 3 · edited Nov 23 '13 at 00:17

1

SELECT INET_ATON('10.0.5.9');

Try this function in yours :)

Perhaps that's not an alternative as it's only for IP address type. And you do have more parts than an IP address have.

edited Nov 23 '13 at 00:17

BenMorel

34,448
50
182
322

answered Dec 18 '12 at 14:01

bonCodigo

14,268
1
48
91

Will that work with strings that are not IP addresses though? Examples given were not. – Cylindric Dec 18 '12 at 14:02
@Cylindric Yes just noticed that ;) only I wish if we had such function beyond IP addresses sort – bonCodigo Dec 18 '12 at 14:03

score 1 · Answer 4 · answered Dec 18 '12 at 14:38

This is not going to be efficient and can be cleaned up, but is one idea for how to do this. It relies on a table called integers with a single column called i with 10 rows with the values 0 to 9.

SELECT somefield    , SUM(POW(100, occurrences - anInteger) * somefield_split) OrderField
FROM
    (SELECT id,anInteger,  somefield, SUBSTRING_INDEX(SUBSTRING_INDEX(somefield, ".", anInteger), ".", -1) AS somefield_split, LENGTH(somefield) - LENGTH(REPLACE(somefield, '.', '')) + 1 AS occurrences
    FROM splittertest, (SELECT a.i*10+b.i AS anInteger FROM integers a, integers b) Sub1
    HAVING occurrences >= anInteger) Sub2
GROUP BY somefield
ORDER BY somefield, OrderField

Idea is the subselect gets a range of numbers from 0 to 99 and splits up the string off dot separated values based on this. It gets the number of dots in the particular string. It then multiplies each bit of the string by 100 to the power of the total number of sections minus the number of this section, and sums on the basis of this.

So 5.10.15 is split to 5, 10 and 15. There are 3 sections so the first is multiplied by 100 to the power of (3 - 0), second is multiplied by 100 to the power of (2 - 0), etc.

This does give strange results if the number of dots varies (ie, the one with more dots will always be larger), but this could be fixed by using the max number of dots on any line when working out the power, rather than the number of dots on that particular line.

Good luck with it!

score 0 · Answer 5 · answered Dec 18 '12 at 13:51

0

create a new column that is equal to this column with a string replace of '.' with '' (nothing). store this new field as a number. order by this field instead in your queries.

if you cannot add a new column to the table, you could process this in PHP etc.. to the same effect for display purposes on a website for example.

http://php.net/manual/en/function.str-replace.php

answered Dec 18 '12 at 13:51

if i replace the '.' with nothing then 1.11 will be equal to 11.1 and to 1.1.1 – razvansg Dec 20 '12 at 08:26
ok you could split 1.1.1 into three columns and then you will need to order by column 1,2,3 and use a group by in your statement. – Dec 20 '12 at 08:44

score 0 · Answer 6 · edited Dec 18 '12 at 14:19

0

you can use the following sql

SELECT REPLACE(column_name,'.','') AS column_name1 FROM table_name ORDER BY column_name1;

edited Dec 18 '12 at 14:19

Wouter J

41,455
15
107
112

answered Dec 18 '12 at 14:00

dkkumargoyal

556
3
10

Ledhund · Answer 7 · 2012-12-18T14:21:27.373

0

If you are ordering by the last digit group, then

SELECT CAST(MID(order_column, 
CHAR_LENGTH(order_column) - 
LOCATE('.',REVERSE(order_column))+2) AS DECIMAL) AS order_column_as_number
FROM thetable 
ORDER BY order_column_as_number

seems to work.

Doesn't work at all if there are strings without '.' in them, and you would have to extract more parts to order by if you want to order by other groups as well, but this could be a start if you really want it done in sql only.

edited Dec 18 '12 at 14:21

answered Dec 18 '12 at 14:03

Ledhund

1,228
10
24

It's not just the last digit group though. It looks more like "sort by first group, then second group, then third group" etc. – Cylindric Dec 18 '12 at 14:04
Also it fails for the example not containing a '.'. I suppose one could extract more stuff to order by this way, but it would become messy pretty quick. – Ledhund Dec 18 '12 at 14:13

score 0 · Answer 8 · answered Dec 18 '12 at 14:06

0

make sure your column of those numbers is INT not varchar.

if you use varchar your column will be sorted by those who have 1 first and so on ...

answered Dec 18 '12 at 14:06

echo_Me

37,078
5
58
78

dont do any extra code , just do what i told you , change the column of those numbers to `INT` not char or varchar – echo_Me Dec 18 '12 at 14:24

score 0 · Answer 9 · answered May 21 '19 at 13:55

Here my solution for numbers with max 4 decimals :

SELECT myCol, SUBSTRING_INDEX(myCol, ',', 1) * 10000 + LEFT(CONCAT(SUBSTRING_INDEX(myCol, ',', -1), '0000'), 4) as toOrder 
FROM `myTable`
ORDER BY toOrder DESC

You can etit that for 8 decimals like this :

SELECT myCol, SUBSTRING_INDEX(myCol, ',', 1) * 100000000 + LEFT(CONCAT(SUBSTRING_INDEX(myCol, ',', -1), '00000000'), 8) as toOrder 
FROM `myTable`
ORDER BY toOrder DESC

etc...

score 0 · Answer 10 · answered Aug 03 '20 at 02:23

0

Order by INSTR first, then order by replacing "."

SELECT content_id FROM TEST ORDER BY INSTR(`content_id`, '.'), replace(`content_id`,'.','')

SQLfiddle

answered Aug 03 '20 at 02:23

Hariadi

606
10
17

mysql order string as number

10 Answers10

Linked