SQL字符类型排序的问题

[ztao1212]

有个数据表：规格型号类型是字符类型；如下
10*20
10*15
10*10
10*9
10*8
8
6.5*1500
排序的时候我想得到如下结果
6.5*1500
8
10*20
10*15
10*10
10*9
10*8
按照从小到大排序

[szm341]

create table #a(id varchar(20))

insert into #a
select '6.5*100'
union
select '8'
union
select '10*1'
union
select '10*2'
union
select '10*10'
union
select '10*25'
union
select '10*11'

select * from #a 
order by case when charindex('*',id)>0 then cast(left(id,charindex('*',id)-1) as decimal(10,2))
else cast(id as int) end,
cast(substring(id,charindex('*',id)+1,len(id)) as int) desc

/*
6.5*100
8
10*25
10*11
10*10
10*2
10*1
*/

[我腫了]

USE test
GO

-->生成表tb

if object_id('tb') is not null 
	drop table tb
Go
Create table tb([col1] nvarchar(8))
Insert into tb
Select N'10*20'
Union all Select N'10*15'
Union all Select N'10*10'
Union all Select N'10*9'
Union all Select N'10*8'
Union all Select N'8'
Union all Select N'6.5*1500'


SELECT 
		*
	FROM tb
	ORDER BY 
		CONVERT(NUMERIC(38,18),LEFT(col1,PATINDEX('%[^0-9.]%',col1+'*')-1)) ASC
		,CONVERT(NUMERIC(38,18),STUFF(col1,1,PATINDEX('%[^0-9.]%',col1),'')) DESC

/*
col1
--------
6.5*1500
8
10*20
10*15
10*10
10*9
10*8
*/
Go