Varchar Order By 1

[BigHans]

Hi!

After a vast amount of hunting I've come to no resolution to my dilemma. I have a varchar(20) table column containing values similar to this:

Alpha 1
Alpha 2
Alpha 3
Alpha 10
Alpha 12
Beta 1
Beta 2
Beta 3
Beta 15
...
...

With a straight forward ORDER BY field it outputs the following:

Alpha 1
Alpha 10
Alpha 12
Alpha 2
Alpha 3
Beta 1
Beta 15
Beta 2
Beta 3
...
...

I tried ORDER BY field+0, which returned exactly the same thing. I have also tried CAST to no avail.

Please can someone suggest what I should try next?

Thanks in advance

 

[SimonSellick]

You really need two columns here, so that the number can be treated as such. You can split the column for the ORDER BY clause by looking for the embedded space (assuming that it is always there):

...
ORDER BY
  LEFT(column, LOCATE(' ', column)) -- the alpha part
, CONVERT(SUBSTRING(column, LOCATE(' ', column) + 1), UNSIGNED) -- the number

HTH
Simon

 

[BigHans]

Ideally I would have two columns but I do have other values in this column (int, decmial etc). For this ORDER BY instance the selected field will always have a space so, thank you for your help here it works perfectly!

 

[r937]

instead of LOCATE and SUBSTR, in mysql you can use SUBSTRING_INDEX

ORDER 
    BY SUBSTRING_INDEX(column,' ',1) 
     , CAST(SUBSTRING_INDEX(column,' ',-1) AS UNSIGNED)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon