Help with order by for strings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with order by for strings

Author

Topic

edp33
Starting Member

9 Posts

Posted - 2007-03-10 : 18:02:15

I have a column containing reference numbers in the form of

1
1.1
1.1.1
1.1.2
2
2.1
etc

I would like to display these in order like above, however when doing an 'order by' on the column it comes out as
1
1.1
1.2
10
10.10
10.11
2
etc

Can anyone recommend a technique for ordering this type of string?

Many thanks

James

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-10 : 18:23:38

[code]
declare @table table
(
col varchar(10)
)

insert into @table
select '1' union all
select '1.1' union all
select '1.1.1' union all
select '1.1.2' union all
select '10' union all
select '10.11' union all
select '10.10' union all
select '2' union all
select '2.1'

select *
from @table
order by convert(int, dbo.fnParseString(-1, '.', col)),
convert(int, dbo.fnParseString(-2, '.', col)),
convert(int, dbo.fnParseString(-3, '.', col))

/*
col
----------
1
1.1
1.1.1
1.1.2
2
2.1
10
10.10
10.11

*/
[/code]

Using fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033&SearchTerms=fnParseString

edp33
Starting Member

9 Posts

Posted - 2007-03-10 : 20:18:09

Many thanks KH that's just what I needed :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources