How to ORDER BY 'name + 0' in codeIgniter (mysql)
<p>I have a table in a MySQL database that looks like this:</p>

<pre><code>id | name
1  | 1 some words
2  | 2 some other words
3  | 1.1 some other words
...
10 | 10 some other words
</code></pre>

<p>If I sort the table using following method:</p>

<pre><code>$this-&gt;db-&gt;select('*')
    -&gt;order_by('name', 'ASC')
    -&gt;get('table_name');
</code></pre>

<p>I receive the table in following order:</p>

<pre><code>id | name
1  | 1 some words
3  | 1.1 some other words
10 | 10 some other words
...
2  | 2 some other words
</code></pre>

<p>But I actually want to receive the table in this order:</p>

<pre><code>id | name
1  | 1 some words
3  | 1.1 some other words
2  | 2 some other words
...
10  | 10 some other words
</code></pre>

<p>This is possible using following SQL statement:</p>

<pre><code>SELECT * FROM database_name.table_name ORDER BY name + 0 ASC;
</code></pre>

<p>But if I use codeIgniters query builder like this I get a database error:</p>

<pre><code>$this-&gt;db-&gt;select('*')
    -&gt;order_by('name + 0', 'ASC')
    -&gt;get('table_name');
</code></pre>

<p>Note that it is not possible in my situation to either store the numbers in a different column or order by id.</p>

<p>So is there a way to make this SQL statement work in CodeIgniters query builder?</p>

<pre><code>SELECT * FROM database_name.table_name ORDER BY name + 0 ASC;
</code></pre>

<p>Thanks in advance</p>

<p><strong>EDIT:</strong>
I am very sorry for the confusion but the '.' in 1.1 was not meant to be a floating point but as a dot like in: 1.1.1, 1.1.2, 1.1.3
I have found a solution using @Marc B solution and put it into the query builder like this:</p>

<pre><code>$query = $this-&gt;db-&gt;select('name+0  AS name', FALSE)           
    -&gt;order_by('name', 'ASC')
    -&gt;get('table_name');
</code></pre>

<p>Thank you all very much for your answers</p>