Sorting text values with mulitple decimals!? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Sorting text values with mulitple decimals!?

Author

Topic

djchrome
Starting Member

19 Posts

Posted - 2002-09-13 : 11:42:09

You have a field in a SQL Server database that is defined as a varchar(32). The field contains values like 22.2.6 and 22.2.10 and 22.6.1. Using only SQL commands in a stored procedure, how would you sort the following records so they come out in the correct order?

Values to sort:
22.6.1
2.1.1
22.2.6
22.2.10.2
22.2.10

Correct order:
2.1.1
22.2.6
22.2.10
22.2.10.2
22.6.1

skond
Yak Posting Veteran

55 Posts

Posted - 2002-09-13 : 11:50:57

suppose your table looks like this

table abc
(
xyz varchar(30)
)

select xyz from abc
order by cast(replace(xyz,'.','') as bigint)

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-09-13 : 12:21:32

Depending on the number of decimals, this may work:


set nocount on

create table #tmp (TheData varchar(32) not null)

insert #tmp (TheData) values ('22.6.1')
insert #tmp (TheData) values ('2.1.1')
insert #tmp (TheData) values ('22.2.6') 
insert #tmp (TheData) values ('22.2.10.2') 
insert #tmp (TheData) values ('22.2.10') 

print '** Not sorted correctly **'
select * from #tmp order by TheData


print ''
print '** Sorted correctly **'

select TheData 
from #tmp
order by 
case when PARSENAME(TheData, 4) is null
then
	REPLICATE('0', 3 - LEN(PARSENAME(TheData, 3))) + PARSENAME(TheData, 3) + 
	REPLICATE('0', 3 - LEN(PARSENAME(TheData, 2))) + PARSENAME(TheData, 2) + 
	REPLICATE('0', 3 - LEN(PARSENAME(TheData, 1))) + PARSENAME(TheData, 1) +
	'.000'
else

	REPLICATE('0', 3 - LEN(PARSENAME(TheData, 4))) + PARSENAME(TheData, 4) + 
	REPLICATE('0', 3 - LEN(PARSENAME(TheData, 3))) + PARSENAME(TheData, 3) + 
	REPLICATE('0', 3 - LEN(PARSENAME(TheData, 2))) + PARSENAME(TheData, 2) + 
	REPLICATE('0', 3 - LEN(PARSENAME(TheData, 1))) + PARSENAME(TheData, 1)
end

drop table #tmp

/* here are the results */

** Not sorted correctly **
TheData                          
-------------------------------- 
2.1.1
22.2.10
22.2.10.2
22.2.6
22.6.1

 
** Sorted correctly **
TheData                          
-------------------------------- 
2.1.1
22.2.6
22.2.10
22.2.10.2
22.6.1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources