27,579
社区成员
发帖
与我相关
我的任务
分享
declare @Fields table (ID int,cate varchar(16))
insert into @Fields
select 1,'6BH45' union all
select 2,'4BH_8' union all
select 3,'BH-3' union all
select 4,'AA-9-19' union all -- !!
select 5,'KDK9' union all
select 6,'2349' union all --!!
select 7,'-2349' union all
select 8,'-23A49' union all
select 9,'KDK-2'
select *,
stuff( right(cate, patindex('%[^0-9]%',reverse('A'+cate))),
1,
case isNumeric(right(cate, patindex('%[^0-9]%',reverse('A'+cate)))) when 1 then 0 else 1 end,
''
) as px
from @Fields
order by px
/*
ID cate px
1 6BH45 45
2 4BH_8 8
3 BH-3 -3
4 AA-9-19 -19
5 KDK9 9
6 2349 2349
7 -2349 -2349
8 -23A49 49
9 KDK-2 -2
*/
/*
ID cate px
1 6BH45 45
2 4BH_8 8
3 BH-3 -3
4 AA-9-19 -19
5 KDK9 9
6 2349 2349
7 -2349 -2349
8 -23A49 A49
9 KDK-2 -2
*/
declare @Fields table (ID int,cate varchar(16))
insert into @Fields
select 1,'6BH45' union all
select 2,'4BH_8' union all
select 3,'BH-3' union all
select 4,'AA-9-19' union all -- !!
select 5,'KDK9' union all
select 6,'2349' union all --!!
select 7,'-2349' union all
select 8,'-23A49' union all
select 9,'KDK-2'
select *,
stuff(
right(cate, case patindex('%[^0-9]%',reverse(cate)) when 0 then len(cate) else patindex('%[^0-9]%',reverse(cate)) end),
1,
case when patindex('%[^0-9-]%',cate)=0 or charindex('-',cate)>0 then 0 else 1 end,
''
)
from @Fields
declare @Fields table (ID int,cate varchar(16))
insert into @Fields
select 1,'6BH45' union all
select 2,'4BH8' union all
select 3,'BH-3' union all
select 4,'AA-9-19' union all -- ??
select 5,'KDK9' union all
select 6,'KDK-2'
select *,
cast(
case
when left(right(cate,patindex('%[^0-9]%',reverse(cate))),1)!='-'
then right(cate,patindex('%[^0-9]%',reverse(cate))-1)
else
right(cate,patindex('%[^0-9]%',reverse(cate)))
end as int) as px
from @Fields
order by 3
/**
ID cate px
----------- ---------------- -----------
4 AA-9-19 -19
3 BH-3 -3
6 KDK-2 -2
2 4BH8 8
5 KDK9 9
1 6BH45 45
(6 行受影响)
**/
use tempdb
go
Alter FUNCTION [dbo].[GET_NUMBER4] ( @S VARCHAR(100) )
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @i INT
SET @i=LEN(@S)
WHILE (@i>0)
BEGIN
IF Not (PATINDEX('%[0-9-]%', SUBSTRING(@s,@i,1))>0)
RETURN SUBSTRING(@s,@i+1,LEN(@s)-1)
IF (PATINDEX('%[-]%', SUBSTRING(@s,@i,1))>0)
RETURN SUBSTRING(@s,@i,LEN(@s))
SET @i=@i-1
END
RETURN @S
END
go
declare @Fields table (ID int,cate varchar(16))
insert into @Fields
select 1,'6BH45' union all
select 2,'4BH8' union all
select 3,'BH-3' union all
select 4,'AA-9-9' union all -- ??
select 5,'KDK9' union all
select 6,'KDK-2'
select ID,cate,[dbo].[GET_NUMBER4](cate) AS num from @Fields
ORDER BY CAST([dbo].[GET_NUMBER4](cate) AS int)
create FUNCTION [dbo].[GET_NUMBER4] ( @S VARCHAR(100) )
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @i INT
SET @i=LEN(@S)
WHILE (@i>0)
BEGIN
IF(PATINDEX('%[0-9-]%', SUBSTRING(@s,@i,1))>0)
SET @i=@i
ELSE
RETURN SUBSTRING(@s,@i+1,LEN(@s)-1)
SET @i=@i-1
END
RETURN @S
END
go
declare @Fields table (ID int,cate varchar(16))
insert into @Fields
select 1,'6BH45' union all
select 2,'4BH8' union all
select 3,'BH-3' union all
select 4,'AA-9' union all
select 5,'KDK9' union all
select 6,'KDK-2'
select ID,cate,[dbo].[GET_NUMBER4](cate) AS num from @Fields
ORDER BY CAST([dbo].[GET_NUMBER4](cate) AS int)
/*
ID cate num
----------- ---------------- ------------
4 AA-9 -9
3 BH-3 -3
6 KDK-2 -2
2 4BH8 8
5 KDK9 9
1 6BH45 45
--自定义函数
CREATE FUNCTION [dbo].[GET_NUMBER] ( @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
declare @Fields table (ID int,cate varchar(16))
insert into @Fields
select 1,'BH4' union all
select 2,'BH8' union all
select 3,'BH-3' union all
select 4,'AA-9' union all
select 5,'KDK9' union all
select 6,'KDK-2'
select ID,cate,[dbo].[GET_NUMBER](cate) AS num from @Fields
ORDER BY [dbo].[GET_NUMBER](cate)
/*
ID cate num
----------- ---------------- -------------
6 KDK-2 2
3 BH-3 3
1 BH4 4
2 BH8 8
4 AA-9 9
5 KDK9 9
*/
declare @tb table(id int,cate varchar(50))
insert @tb
select 1 ,'BH4'
union all select 2 ,'BH8'
union all select 3 ,'BH-3'
union all select 4 ,'AA-9'
union all select 5 ,'KDK9'
union all select 6 ,'KDK-2'
select *,SUBSTRING(cate,PATINDEX('%[0-9]%',cate),LEN(cate)) num
from @tb
order by num
--结果:
id cate num
6 KDK-2 2
3 BH-3 3
1 BH4 4
2 BH8 8
4 AA-9 9
5 KDK9 9
--> 测试数据: [Fields]
if object_id('[Fields]') is not null drop table [Fields]
go
create table [Fields] ( cate varchar(15))
insert into [Fields]
select 'BH4' union all
select 'BH8' union all
select 'BH-3' union all
select 'AA-9' union all
select 'KDK9'union all
select 'KDK-2'
select * from [Fields] order by cast(right(cate,len(cate)-patindex('%[0-9]%',cate)+1) as int)
cate
---------------
KDK-2
BH-3
BH4
BH8
AA-9
KDK9
(6 行受影响)