征求排序最佳实现

[FlowerJunjie]

我有一个列
练习1
练习2
练习3
2008练习4
2008练习5


.....


要求按最后的数字排序，
练习12
练习11
2008练习5
2008练习4
练习3
练习2
练习1

如何才能最有效的截取后面的数字呢？

[liangCK]

最后的固定是数字吗?

[liangCK]

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-14 13:26:06
-------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (col VARCHAR(9))
INSERT INTO @T
SELECT '练习1' UNION ALL
SELECT '练习2' UNION ALL
SELECT '练习3' UNION ALL
SELECT '2008练习4' UNION ALL
SELECT '2008练习5' UNION ALL
SELECT '练习12' UNION ALL
SELECT '练习11' 

--SQL查询如下:

SELECT * FROM @T
ORDER BY CAST(RIGHT(col,PATINDEX('%[^0-9]%',REVERSE(col))-1) AS money) DESC

/*
col
---------
练习12
练习11
2008练习5
2008练习4
练习3
练习2
练习1

(7 行受影响)
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-14 13:26:16
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(9))
insert [tb]
select '练习1' union all
select '练习2' union all
select '练习3' union all
select '2008练习4' union all
select '2008练习5'
--------------开始查询--------------------------

select * from [tb] order by right(col,(len(col)-charindex('练习',col)))desc 
----------------结果----------------------------
/*col
---------
2008练习5
2008练习4
练习3
练习2
练习1

(5 行受影响)
 
*/

[FlowerJunjie]

不固定，也可能没有数字

[lihan6415151528]

create table test(name varchar(20))
insert test 
select '练习1' union all
select '练习2' union all
select '练习3' union all
select '2008练习5' union all
select '练习12' union all
select '2008练习4' 
go

create function dbo.F_Get_Number (@S varchar(100))
returns int
AS 
begin
    while PATINDEX('%[^0-9]%',@S)>0
          begin
                set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
          end
    return cast(@S as int)
end
GO


select dbo.F_Get_Number(name) from test

            
----------- 
1
2
3
20085
12
20084

（所影响的行数为 6 行）

[FlowerJunjie]

前面的汉字也不是固定的

[lihan6415151528]

可以把‘练习’左边的全部去掉，之后再排序

[billpu]

SUBSTRING(col,CHARINDEX('练习',col)+2,1000)

[soft_wsx]

没看明白，继续

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-14 13:29:39
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] varchar(9))
insert [tb]
select '练习12' union all
select '练习11' union all
select '2008练习5' union all
select '2008练习4' union all
select '练习3' union all
select '练习2' union all
select '练习1'
--------------开始查询--------------------------
select * from [tb] order by cast(right(col,(len(col)-charindex('练习',col)-1)) as int)desc 
----------------结果----------------------------
/* col
---------
练习12
练习11
2008练习5
2008练习4
练习3
练习2
练习1

(7 行受影响)
*/

[chuifengde]

SELECT * from [TABLE]
ORDER BY PARSENAME(REPLACE(col,'练习','.'),1)*1 desc

[FlowerJunjie]

也就是字符串末尾如果有数字则截取此数字，按此数字排序

[FlowerJunjie]

如何截取字符串末尾可能存在的数字

[华夏小卒]

[Quote=引用 6 楼 flowerjunjie 的回复:]
前面的汉字也不是固定的
[/Quote]

所以，要把你的情况写全，不写全，估计写的有问题

[FlowerJunjie]

是啊，汗

[htl258_Tony]

[Quote=引用 13 楼 flowerjunjie 的回复:]
如何截取字符串末尾可能存在的数字
[/Quote]

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-14 16:08:25
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
	Mar 29 2009 10:27:29 
	Copyright (c) 1988-2008 Microsoft Corporation
	Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

---------------------------------*/
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb]([col] NVARCHAR(10))
INSERT [tb]
SELECT N'练习12' UNION ALL
SELECT N'练习11' UNION ALL
SELECT N'2008练习5' UNION ALL
SELECT N'2008练习4' UNION ALL
SELECT N'练习3' UNION ALL
SELECT N'练习2' UNION ALL
SELECT N'练习1' UNION ALL
SELECT N'练习'
GO
--SELECT * FROM [tb]

-->SQL查询如下:
SELECT RIGHT(col,PATINDEX('%[^0-9]%',REVERSE(col))-1) AS col FROM tb WHERE ISNUMERIC(RIGHT(col,1))=1
/*
col
----------
12
11
5
4
3
2
1

(7 行受影响)
*/