I know its Java forum, but Java ranch has solved my lot of problem so i even for SQL i am posting my question on Ranch.
Here i have column of type varchar in a table of SQL 2000..... the problem begins with order by queries....... the column usually have data like A) 1,2,3,4,5,10,1F,2F........ when i put order by in my query the result comes as B) 1,10,1F,2,2F,3,4,5.............
i wanted to maintain the order as in (A)..... If any one have any idea please help me.
char_length is a Sybase function isn't it? Sorting of text fields in SQL Server is done based on the collation you use. You can either specify the collation when you create the database, in which case the sorting rules are applied to every text field, or you can specify the collation you want to use as part of the sql statement:
Of the top of my head I can't think of a collation that sorts as you want, but this page lists them.
Ok....Paul But That's long way..and needed table alter.... i was in search of any trick that i can put in query..... any way ...... One Solution i have found .....but it is too not working correctly i am still looking for solution....
[ June 14, 2007: Message edited by: vijay saraf ] [ June 14, 2007: Message edited by: vijay saraf ]
If you use SQL Server at all, you are using collations, so they are a good thing to learn about if you are doing much work with this DB. It's worth remembering though that if your application relies on this type of ordering you make your code database dependent, so you could also check to see if you can return your results unordered and order them in your applciation.
I have tried your code. but i was getting exceptio as
Then i search on the net and from Page i got the function as bellow ;
This is working But result comes as in the following order 1F,2F,1.2.....9,10,.....20,21,21A,21B...... I am little bit confused why some alphanumerics are aprearing at front and some at rear. As i am not master in Database coding this code looks like " ant on paper " to me. If you can suggest some thing to please help.
Your example data contained only hexadecimal values (1,2,3,4,5,10,1F,2F..). That's why I proposed to use a method that converts them to decimal, and sort on that.
If your column does not contain hexadecimals, you will have to to split your column value in two parts, the numerical part and the alpha part (maybe by creating two stored functions getNum() and getAlpha()). First sort on the numerical part, then sort on the alpha part.