Click here to Skip to main content
15,890,282 members
Please Sign up or sign in to vote.
3.67/5 (3 votes)
See more:
I have a db with a table (text format) with numbers.
I want to sort it

<br />
1.1<br />
1.2<br />
1.1.1<br />
1.2.1<br />
1.1.2<br />
1.2<br />
1.2.1<br />
1.22.11<br />
1.3<br />
1.4<br />
etc<br />


I want it to come up like this after sorting:

<br />
1.1<br />
1.1.1<br />
1.1.2<br />
1.2<br />
1.2.1<br />
1.2.2<br />
1.22.11<br />
1.3<br />
1.4<br />
etc<br />


Is it possible and how? Thanks in advance
Posted
Updated 22-Mar-11 4:47am
v3
Comments
Pavel Yermalovich 22-Mar-11 9:21am    
Is 1.22.11 a floating number?
Dalek Dave 22-Mar-11 10:47am    
Edited for Grammar.

One straight-forward way is to create a small procedure which would use temporary table, split the strings into pieces (numeric most likely) and then fetch, sort and concatenate the data and return the result set to the client. The procedure can be done in T-SQL but considering that you are going to play with strings, it would be easier to use C# to create the procedure.
 
Share this answer
 
Comments
Dalek Dave 22-Mar-11 10:48am    
Sage Advice
Wendelius 22-Mar-11 12:13pm    
Thanks :)
This example might help you.
Please enhance this code as per your requirement.

//Code for creating table can be ignored if you are fetching data from DB.
string[] items = { "1.1", "1.2", "1.1.1", "1.2.1", "1.1.2", "1.2", "1.2.1", "1.22.11", "1.3", "1.4" };
DataTable dtItem = new DataTable();
dtItem.Columns.Add("tab_index", typeof(string));
foreach (string item in items)
{
    dtItem.Rows.Add(item);
}
dtItem.AcceptChanges();
//Now just assign this to DataView and sort the same, column has to be string
DataView dvItem = dtItem.DefaultView;
dvItem.Sort = "tab_index";

foreach (DataRowView viewItem in dvItem)
{
    System.Diagnostics.Debug.WriteLine(viewItem["tab_index"].ToString());
}
 
Share this answer
 
v3
Comments
Dalek Dave 22-Mar-11 10:50am    
Edited for Code Block, but a good answer.
avigodse 22-Mar-11 11:10am    
thx a lot Dalek. :)
SQL
select columnname from table order by columnname


EDIT ==========

So why did you vote my answer a 2? Did it not work?
 
Share this answer
 
v2
Comments
Dalek Dave 22-Mar-11 10:49am    
I'll countervote that John, it seems unfair, as it is a reasonable answer.
#realJSOP 22-Mar-11 10:52am    
But does it give him what he wants? I didn't bother testing it because I don't want to pollute our database with temporary tables.
kishore Rajendran 8-Apr-11 3:14am    
This method is good,but we have to extract numbers only from that column and then put orderby
hi,

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
 
Share this answer
 
Comments
kishore Rajendran 8-Apr-11 3:15am    
no we can do anything,not a single one is impossible..we have 100 ways to do each query

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900