--按数值 select * from table1 order by convert(int,left(字符字段,charindex('号',字符字段)))
晕了,又忘记-1了 select * from table1 order by convert(int,left(字符字段,charindex('号',字符字段)-1))
select * from table order by substring(字段,0,charindex('号',字段))
declare @s varchar(10) set @s='10號'select cast(left(@s,len(@s)-1) as int)
declare @s varchar(10) set @s='10號'select * from 表名 cast(left(字段,len(字段)-1) as int)
select * from ytable order by substring(ycol,1,len(ycol) - 1)
--创建通用函数 create function f_test(@inchar varchar(50)) returns numeric(10) as begin declare @i int declare @v char(1) set @i = 0 while(@i<len(@inchar)) begin set @v = substring(@inchar,@i+1,1) if(isnumeric(@v) = 1) begin set @i = @i+1 end else begin break; end end return(cast((case when substring(@inchar,1,@i) = '' then null else substring(@inchar,1,@i) end) as numeric(10))) end go --执行查询 select * from tabname order by dbo.f_test(colname)
select dd = substring(字符字段,1,2),* from table order by 1 asc
select * from tablename order by cast(substring(字段,patindex('%[0123456789]%',字段),patindex('%[^0^1^2^3^4^5^6^7^8^9]%',字段)-1) as int)
select * from table1 order by convert(int,left(字符字段,charindex('号',字符字段)))
select * from table1 order by convert(int,left(字符字段,charindex('号',字符字段)-1))
from table
order by substring(字段,0,charindex('号',字段))
set @s='10號'select cast(left(@s,len(@s)-1) as int)
set @s='10號'select * from 表名 cast(left(字段,len(字段)-1) as int)
create function f_test(@inchar varchar(50))
returns numeric(10)
as
begin
declare @i int
declare @v char(1)
set @i = 0
while(@i<len(@inchar))
begin
set @v = substring(@inchar,@i+1,1)
if(isnumeric(@v) = 1)
begin
set @i = @i+1
end
else
begin
break;
end
end
return(cast((case when substring(@inchar,1,@i) = '' then null else substring(@inchar,1,@i) end) as numeric(10)))
end
go
--执行查询
select * from tabname order by dbo.f_test(colname)
from table
order by 1 asc
有这方面的资料没