I would like to return rows in a custom order, similar to this post. However, in my case I do not have a fixed number of 'sort by' items. The order I would like to have returned from Col2 is:
- 'A' Numeric Values in numeric order 0-99 All other alpha values
'A', '0', '1', '3', '65', 'MyValue1', 'MyValue2'
How could I achieve this?
thanks
Richard
Here You Go...
Code Snippet
Create Table #sorttest (
[Col2] Varchar(100)
);
Insert Into #sorttest Values('A');
Insert Into #sorttest Values('12');
Insert Into #sorttest Values('1');
Insert Into #sorttest Values('3');
Insert Into #sorttest Values('5');
Insert Into #sorttest Values('6');
Insert Into #sorttest Values('78');
Insert Into #sorttest Values('100');
Insert Into #sorttest Values('MyValue1');
Insert Into #sorttest Values('MyValue3');
Insert Into #sorttest Values('MyValue2');
Select
*
From
#sorttest
Order By
Case When Col2 Like '[A-Z]' Then 1
When Isnumeric(Col2)=1 Then 2
Else 3 End,
Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,
Col2
/*
::Output
Col2
-
A
1
3
5
6
12
78
100
MyValue1
MyValue2
MyValue3
*/
|||hi, try this
SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) = 0
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE ISNUMERIC(MyMixedField) = 1
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) > 0
and ISNUMERIC(MyMixedField) <> 1|||i think manivannan's solution is much better,
@.manivannan..
it think its better to use patindex('%[0-9]%', col2) = 0 for alpha valued records
Order By
Case When patindex('%[0-9]%', col2) = 0 Then 1
When Isnumeric(Col2)=1 Then 2
Else 3 End,
Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,
Col2|||thank you both very much for your quick and accurate responses!
Works perfectly
|||
It seems that manivannan's suggested use of
Like '[A-Z]'
is most likely the best option to sort on single letter alpha. Don't you think that the
suggestion 'might' include things like an asterisk, period, @. symbol, $ symbol, etc.? (-As well as allowing multi-character alpha entries on the first sort level...)
patindex('%[0-9]%', col2) = 0
However, I would caution that using manivannan's suggestion to use isnumeric() 'could' cause problems. For example, the following evaluates to TRUE
SELECT isnumeric( '$' )
But is it a number? Should it sort with the alphas?
|||HiGood point, but actually in this case we don't allow non-alphanumeric characters in this field, so it's not really an issue!
thanks everyone for help on this
