模糊查询 按字数多少排序

declare @key nvarchar(20)
set @key = '打印机 ibm a'

declare @return nvarchar(100)
declare @sql nvarchar(max)
set @sql  = ''
if charindex(' ',@key) > 0
begin
	while charindex(' ',@key) > 0 
    begin
        select @return = substring(@key,1,charindex(' ',@key) - 1)
		--print @return
		set @sql = @sql +  'select * from (select *,len(search) s1, len(replace(search,'''+@return+''','''')) s2 from vfit) a where s1 != s2 union all '
		--print( @sql)
		select @key = ltrim(stuff(@key,1,charindex(' ',@key),''))
        if charindex(' ',@key) <= 0
        begin
			set @sql = @sql +  'select * from (select *,len(search) s1, len(replace(search,'''+@key+''','''')) s2 from vfit) a where s1 != s2'
            break
        end
    end
	-- 上面循环是拼接需要union 的总数据
	-- 下面是是分组并排序，按倒序排列
	set @sql = 'select * from tfit inner join 
				(
				select fitid ,sum(s1)-sum(s2) as numb from 
				(' + @sql + ') b group by fitid
				) c on tfit.fitid = c.fitid order by c.numb desc'
	--print( @sql)
	exec(@sql)
end
else
begin
	;with c2 as
	(
		select *,len(search) s1, len(replace(search,@key,'')) s2 from vfit 
	)
	select * from c2 where s1 != s2 order by s1-s2 desc
end

这种写法不考虑效率