when Patindex('%[0-9][0-9][0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9][0-9][0-9]'+SubString(Address,len(address),1),address),4)
when Patindex('%[0-9][0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9][0-9]'+SubString(Address,len(address),1),address),3)
when Patindex('%[0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9]'+SubString(Address,len(address),1),address),2)
when Patindex('%[0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9]'+SubString(Address,len(address),1),address),1)
end)),
--select * from table1 order by
convert(int,case
when Patindex('%[0-9][0-9][0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9][0-9][0-9]'+SubString(Address,len(address),1),address),4)
when Patindex('%[0-9][0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9][0-9]'+SubString(Address,len(address),1),address),3)
when Patindex('%[0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9]'+SubString(Address,len(address),1),address),2)
when Patindex('%[0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9]'+SubString(Address,len(address),1),address),1)
Name address
张三 花园新村2幢
李四 花园新村14幢
王五 花园新村1幢
马六 CSDN134幢
用一条sql语句:
select * from table1 order by
substring(address,1,len(address)-1-len(case
When SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1) as int)*1000
else 0
End +
case
When SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1) as int)*100
else 0
End +
case
When SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1) as int)*10
else 0
End +
case
When SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1) as int)
else 0
End )),
case
When SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1) as int)*1000
else 0
End +
case
When SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1) as int)*100
else 0
End +
case
When SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1) as int)*10
else 0
End +
case
When SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1) as int)
2,更正:
declare @a varchar(200),@allNum varchar(20),@i int
set @a='花园新村96614423幢'
set @allNum=''
set @i=0
select @allNum=case when @i=0 then @allNum+substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1) else @allNum end
,@i=case when substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1) not like '[0-9]' then 0 else @i end
from (select @a Address)a left join numtab b on substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1) like '[0-9]'
select reverse( @allNum)
1,建序数表
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c
2,
declare @a varchar(200),@allNum varchar(20)
set @a='花园新村123幢'
set @allNum=''
select @allNum=@allNum+substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1)
from (select @a Address)a left join numtab b on substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1) like '[0-9]'
select reverse( @allNum)
稍微修改一下:
CREATE function getnumber(@a Nvarchar(100))
returns int
as
begin
declare @i int,@len int,@b Nvarchar(100)
set @i=1
set @b=''
set @len=len(@a)
while @i<=@len
begin
if substring(@a,@i,1) like '[0-9]'
set @b=@b+substring(@a,@i,1)
set @i=@i+1
end
return cast(@b,int)
end
select name,address from table
order by dbo.getnumber(address)
CREATE function getnumber(@a Nvarchar(100))
returns Nvarchar(2000)
as
begin
declare @i int,@len int,@b Nvarchar(100)
set @i=1
set @b=''
set @len=len(@a)
while @i<=@len
begin
if substring(@a,@i,1) like '[0-9]'
set @b=@b+substring(@a,@i,1)
set @i=@i+1
end
return @b
end
select * from table
order by address,dbo.getnumber(address)