In an ORDER BY, I am splitting the first number a column into a separate entity so that I can do some specific ordering. example:
| Slices |
|---|
| 2 14 |
| 1 14 |
| 5 14 |
| 4 14 |
| 35 2.6 |
separated into
| NumSlice | DiameterofPies |
|---|---|
| 2 | 14 |
| 1 | 14 |
| 5 | 14 |
| 4 | 14 |
| 35 | 2.6 |
I'm accomplishing this by:
LEFT(SLICES, CHARINDEX(' ', SLICES) - 1) DESC
I'm pretty sure, by the results, it's working as it is supposed to since it orders based off the first character only. example:
| Slices |
|---|
| 1 |
| 2 |
| 35 |
| 4 |
| 5 |
However, I would like it to order by the entire value of '35' instead of '3'.
example:
| Slices |
|---|
| 1 |
| 2 |
| 4 |
| 5 |
| 35 |
But I'm not sure how to go about this. Does anyone have any ideas?
CodePudding user response：
Your value is a string, you are therefore ordering a string, and alphabetically 35 comes after 2 and before 4
If you want the value to be treated as an integer then you need to cast it
ORDER BY CAST(LEFT(SLICES, CHARINDEX(' ', SLICES) - 1) as INT) DESC
I am guessing since you have tagged SSMS you are actually using SQL Server, if there is the possibility the string could contain anything other than digits then you can use TRY_CAST or TRY_CONVERT to prevent errors.
Ideally of course, values that have their own specific meaning should be stored as separate columns of appropriate data types, then the need for string manipulation is avoided completely.
CodePudding user response：
CAST(LEFT(SLICES, CHARINDEX(' ', SLICES) - 1) as int)