1

I am facing problem in sorting the column with nvarchar datatype. How do i sort this in ascending order. Data is in this format...

1/0
22/21
19/26
2.3/14
29/0
1.3/44
85/30

First values is kilometer, either it can be integer or double then a forward slash and the last value is pole number, it will be integer always.

this data is generated by concatenating two columns, i.e 

select fromkm+"/"+frompole as FROM_KM from station;

fromkm and frompole are nvarchar type in database

Result should be in the following formate

1/0
1.3/44
2.3/14
19/26
22/21
29/0
85/30

Thanks

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
M. Usaid
  • 72
  • 1
  • 10
  • 1
    What does this have to do with c#? – Zohar Peled Jan 30 '19 at 11:57
  • It looks like you just want to sort on the first value, but the data you display is made of two values. Ideally you'd do the sort before combining the two values into the display string. What does your query look like? Please show a little of the code that you're using. – Robin Bennett Jan 30 '19 at 11:58
  • You have to split around the forward slash then sort by each item in split array. You can use IComarer like I did at following posting : https://stackoverflow.com/questions/54311284/custom-sort-in-c-sharp-of-an-item-that-is-formatted-like-this-fod11-1/54313065#54313065 – jdweng Jan 30 '19 at 12:00

2 Answers2

3

Do the sort in SQL using order by. Note that since your data is stored as nvarchar you'll have to cast it to float / int when sorting (or better yet - change the data types of the columns in the database):

select fromkm +"/"+ frompole as FROM_KM 
from station
order by cast(fromkm as float), cast(frompole as int);
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Not working.. and i cant change the datatype, because the project is completed and if now i change the data type then i will face series of errors. Somehow I just want to sort this value. – M. Usaid Jan 30 '19 at 12:06
  • Define `Not working`. If you don't change the data type you might face bigger problems when you'll encounter values such as `bla` when you expect a number. – Zohar Peled Jan 30 '19 at 12:08
  • The datatype isnt changed in the table, just for the statement. FROM_KM still will be the same datatype as before (nvarchar?) – FrankM Jan 30 '19 at 12:10
  • Yes, the code I've written should not change anything in the table, just sort the value correctly. – Zohar Peled Jan 30 '19 at 12:11
  • Yes it worked bro.. there is no need to cast fromepole into int.. it working with fromkm only.. select fromkm+"/"+frompole as From_Km from station order by cast(fromkm as float).. this query worked.. Thank you bro @ZoharPeled – M. Usaid Jan 30 '19 at 12:14
  • Well, if you have a result like this: `3/2` and in another row `3/11`, If you want the rows to be ordered where `2` comes before `11`, you do need the cast to int. If you only care about the order of `fromkm` then you don't need the second part of the `order by` at all. – Zohar Peled Jan 30 '19 at 12:16
0

Try the next query, add the order by clause with your field name inside of a Substring function.

SELECT from km+"/"+ from pole as FROM_KM from station;
OEDER BY SUBSTRING(FROM_KM,1,2) ASC
José Teixeira
  • 31
  • 1
  • 6