--创建函数:create function f_split(@name varchar(100),@field varchar(50)) returns varchar(100) as begin while(len(@field)>0) begin set @name=replace(@name,left(@field,1),'') set @field=right(@field,len(@field)-1) end return @name end go--> 测试数据: MESSEAGE create table MESSEAGE(name varchar(100)) insert into MESSEAGE select '南山科技园' union all select '南山电子工业' union all select '深圳南山工业区' union all select '工业大道' union all select '工业区大道' declare @ss varchar(20) set @ss='南山工业区' select * from MESSEAGE where name like '%['+@ss+']%' order by len(name)-len(dbo.f_split(name,@ss)) desc
--创建函数:create function f_split(@name varchar(100),@field varchar(50))
returns varchar(100)
as
begin
while(len(@field)>0)
begin
set @name=replace(@name,left(@field,1),'')
set @field=right(@field,len(@field)-1)
end
return @name
end
go--> 测试数据: MESSEAGE
create table MESSEAGE(name varchar(100))
insert into MESSEAGE
select '南山科技园' union all
select '南山电子工业' union all
select '深圳南山工业区' union all
select '工业大道' union all
select '工业区大道'
declare @ss varchar(20)
set @ss='南山工业区'
select * from MESSEAGE where name like '%['+@ss+']%'
order by len(name)-len(dbo.f_split(name,@ss)) desc