新年第一次提问，数据转换 排序

Question

col1
ss 1.0
ss 2.0
ss 3.0
ss 22AB
ss 4.0
ss 10
ss 23AB
ss 6.0
aaaaaa类似上面的一列 我要排序成下面样式
col1
ss 1.0
ss 2.0
ss 3.0
ss 4.0
ss 6.0
ss 10
ss 22AB
ss 23AB
aaaaaa如果除去最后三行 我使用 order by convert(float,substring(col1,3,100)) 把第三个字符以后转换成 float然后比较排序 但是处理不了最后三行。求高手相助

Answer 1

IF OBJECT_ID('TEMPDB..#')IS NOT NULL DROP TABLE #
GO
CREATE TABLE #(col1 VARCHAR(10)) 
INSERT # SELECT 'ss 1.0' 
INSERT # SELECT 'ss 2.0' 
INSERT # SELECT 'ss 3.0' 
INSERT # SELECT 'ss 22AB' 
INSERT # SELECT 'ss 4.0' 
INSERT # SELECT 'ss 10' 
INSERT # SELECT 'ss 23AB' 
INSERT # SELECT 'ss 6.0' 
INSERT # SELECT 'aaaaaa'
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9-.]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9.]%',@s),1,'')
END
RETURN @S
END
GO
SELECT * FROM # ORDER BY CONVERT(FLOAT,DBO.GET_NUMBER2(col1))
/*col1       
---------- 
aaaaaa
ss 1.0
ss 2.0
ss 3.0
ss 4.0
ss 6.0
ss 10
ss 22AB
ss 23AB*/

Answer 2

IF OBJECT_ID('TEMPDB..#')IS NOT NULL DROP TABLE #
GO
CREATE TABLE #(col1 VARCHAR(10)) 
INSERT # SELECT 'ss 1.0' 
INSERT # SELECT 'ss 2.0' 
INSERT # SELECT 'ss 3.0' 
INSERT # SELECT 'ss 22AB' 
INSERT # SELECT 'ss 4.0' 
INSERT # SELECT 'ss 10' 
INSERT # SELECT 'ss 23AB' 
INSERT # SELECT 'ss 6.0' 
INSERT # SELECT 'aaaaaa'
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9-.]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9.]%',@s),1,'')
END
RETURN @S
END
GO
SELECT * FROM # ORDER BY CASE WHEN  CONVERT(FLOAT,DBO.GET_NUMBER2(col1))>0 THEN CONVERT(FLOAT,DBO.GET_NUMBER2(col1))  ELSE 99999999 END
/*col1       
---------- 
ss 1.0
ss 2.0
ss 3.0
ss 4.0
ss 6.0
ss 10
ss 22AB
ss 23AB
aaaaaa
*/

Answer 3

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(20))
insert [tb]
select 'ss 1.0' union all
select 'ss 2.0' union all
select 'ss 3.0' union all
select 'ss 22AB' union all
select 'ss 4.0' union all
select 'ss 10' union all
select 'ss 23AB' union all
select 'ss 6.0' union all
select 'aaaaaa  '---创建去掉字母的函数---
create function dbo.fn_convertOrder(
@s varchar(100)
) 
returns varchar(100)
as 
begin
while PATINDEX ( '%[A-Z]%' , @s )>0
begin
set @s=stuff(@s,PATINDEX ( '%[A-Z]%' , @s ),1,'')
if len(ltrim(rtrim(@s)))=0 
  set @s='999999999'
end 
return ltrim(rtrim(@s))
end---查询---
select * from [tb]
order by cast(dbo.fn_convertOrder(col1) as float)---结果---
col1                 
-------------------- 
ss 1.0
ss 2.0
ss 3.0
ss 4.0
ss 6.0
ss 10
ss 22AB
ss 23AB
aaaaaa  （所影响的行数为 9 行）

Answer 4

CREATE TABLE #(col1 VARCHAR(10)) 
INSERT # SELECT 'ss 1.0' 
INSERT # SELECT 'ss 2.0' 
INSERT # SELECT 'ss 3.0' 
INSERT # SELECT 'ss 22AB' 
INSERT # SELECT 'ss 4.0' 
INSERT # SELECT 'ss 10' 
INSERT # SELECT 'ss 23AB' 
INSERT # SELECT 'ss 6.0' 
INSERT # SELECT 'aaaaaa'select * from #
order by left(col1,3) desc,right('00000000000'+replace(right(col1,len(col1)-3),'.0',''),10)drop table #
/*
col1       
---------- 
ss 1.0
ss 2.0
ss 3.0
ss 4.0
ss 6.0
ss 10
ss 22AB
ss 23AB
aaaaaa
*/

Answer 5

出现个新情况啊 
出现 varchar to float出错 可能是我数据里面有转换过程中会出错的

Answer 6

用8楼的方法，你试试
select 
  cast(dbo.fn_convertOrder(col1) as float) as col1
from [tb]
看看结果是什么？

Answer 7

CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
declare @i int,
             @a varchar(50)
set @i=PATINDEX('%[0-9-.]%',@S) 
set @a=substring(@s,@i,50)set @i=PATINDEX('%[^0-9-.]%',@a) 
if @i<>0
set @a=substring(@a,0,@i)
else
set @a=substring(@a,0,50)
RETURN @a
END
借用各位写出来的

Answer 8

你肯定有一个排序的规则吧
规则怎么不说。
我想问下
那如果col1 
aa 1.0
bb 1.0
dd 1.0
ss 1.0 
ss 1.0ABC 
ss 2.0 
ss 3.0 
ss 22AB 
ss 4.0 
ss 10 
ss 23AB 
ss 6.0 
aaaaaa 这种是要什么排呢
觉得规则不说。
什么写呢