title: Mysql order by varchar mixed value
body: <p>I have this values in Mysql table:</p>
  
  <pre><code>id    name (varchar 100)
  1     CHANCE EAU TENDRE
  2     CRISTALLE
  3     EGOISTE
  4     1- COCO
  5     1- COCO NOIR
  6     ALLURE
  7     2- N.5
  8     3- COCO HOMME
  9     - COCO CHANEL
  ...
  </code></pre>
  
  <p>I can't figure out how order by name, I've tried to search without success but didn't works.
  Of course, I want <strong>ALLURE</strong> before <strong>CHANCE EAU TENDRE</strong></p>
  
  <p>Expected result:</p>
  
  <pre><code>| id  | name              |
  | --- | ----------------- |
  | 6   | ALLURE            |
  | 1   | CHANCE EAU TENDRE |
  | 4   | 1- COCO           |
  | 9   | - COCO CHANEL     |
  | 8   | 3- COCO HOMME     |
  | 5   | 1- COCO NOIR      |
  | 2   | CRISTALLE         |
  | 3   | EGOISTE           |
  | 7   | 2- N.5            |
  </code></pre>
  
  <p>My tests:</p>
  
  <pre><code>SELECT * FROM brands ORDER BY CAST(name AS UNSIGNED), name ASC
  </code></pre>
  
  <hr>
  
  <pre><code>SELECT * FROM brands ORDER BY convert(`name`, decimal) ASC
  </code></pre>
  
  <hr>
  
  <pre><code>SELECT * FROM brands ORDER BY (name * 1) ASC
  </code></pre>
  
  <p>Those doesn't works because mixed value (number + char).</p>
  
  <p>EDIT: 
  add this link for understand better the question:
  <a href="http://sqlfiddle.com/#!9/8f6e9a/2" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/8f6e9a/2</a> </p>