I'm sorry, i dont know where this would go, but i hope you can help me out with this

If i have a table with a varchar column called layer, then in that column I have the values 1.0, 1.2, 1.3, 5.51, 9.41, 9.51kl, 15.51, and 15.51h, how could i get it to acctually sort it in that way

so far, if i try to sort it, it comes up with the 1's, 15's, the 5 value, and finally, the 9's

I know there is a way to do this with SQL syntax, but i am not that far into it...yet.

Thanks in advance
~Schreck

There is no way since you are sorting strings. You might be able to manipulate the data, but without knowing what data you will be putting into the table I can't help you.

Originally posted by schreck84
I'm sorry, i dont know where this would go, but i hope you can help me out with this

If i have a table with a varchar column called layer, then in that column I have the values 1.0, 1.2, 1.3, 5.51, 9.41, 9.51kl, 15.51, and 15.51h, how could i get it to acctually sort it in that way

so far, if i try to sort it, it comes up with the 1's, 15's, the 5 value, and finally, the 9's

I know there is a way to do this with SQL syntax, but i am not that far into it...yet.

Thanks in advance
~Schreck It may work if you try casting the layer column to a different data type i.e ORDER BY CAST('layer' AS FLOAT) ASC though I'm pretty sure that it won't actually work going from a varchar to a float.

SQL Statement:

Select layer, Cast(Case When patindex('%[^0-9.]%', layer) > 0 Then substring(layer, 1, patindex('%[^0-9.]%', layer) - 1) Else layer End As Float)
From tblTest
Order By Cast(Case When patindex('%[^0-9.]%', layer) > 0 Then substring(layer, 1, patindex('%[^0-9.]%', layer) - 1) Else layer End As Float)



Output:

1.0 1.0
1.2 1.2
1.3 1.3
5.51 5.5099999999999998
9.41 9.4100000000000001
9.51kl 9.5099999999999998
15.51 15.51
15.51h 15.51

Why not break the layers out into a seperate table such as

levels
- level_id
- name
- sort_order

and then strore the layers in the name field and reference the level_id in the existing table? To handle the sort you just do a join and order by.

oops, the table "levels" and references to it in my previous post should actually be "layers".

You can solve problem by adding zeroes, but make sure length of all values is the same.

declare
@tmp table(t varchar(10))

insert into @tmp(t) values('0001.0')
insert into @tmp(t) values('0001.2')
insert into @tmp(t) values('0001.3')
insert into @tmp(t) values('005.51')
insert into @tmp(t) values('009.41')
insert into @tmp(t) values('09.51kl')
insert into @tmp(t) values('015.51')
insert into @tmp(t) values('015.51h')

select * from @tmp order by t

Result

0001.0
0001.2
0001.3
005.51
009.41
015.51
015.51h
09.51kl

It is easy to understand if you write it as a user defined function.


/* Fail Safe Convert SQL Server Varchar To Float User Function */

CREATE FUNCTION [dbo].[ConvertVarcharToFloat] (@str varchar(128))
RETURNS Float AS
BEGIN
Return (Cast(
Case When patindex('%[^0-9.]%', @str) > 0
Then substring(@str, 1, patindex('%[^0-9.]%', @str) - 1)
Else @str
End
As Float))
END



Select layer
From tblTest
Order By dbo.ConvertVarcharToFloat(layer)