declare @TYPE nvarchar(200) ='a' declare @sql nvarchar(200) set @sql='select * from tb where type like %'+@TYPE+'%'print @sql EXEC(@sql)
SELECT * FROM tb WHERE type LIKE '%A%' OR type LIKE '%B%' OR type LIKE '%C%' ORDER BY CASE WHEN PATINDEX('%A%', type) > 0 THEN 1 WHEN PATINDEX('%B%', type) > 0 THEN 2 WHEN PATINDEX('%C%', type) > 0 THEN 3 END
select *,case when type like '%A%' then 1 when type like '%B%' then 2 when type like '%C%' then 3 end x from tb a where type like '%A%' or type like '%B%' or type like '%C%' order by (select count(1) from tb where (type like '%A%' or type like '%B%' or type like '%C%') and case when type like '%A%' then 1 when type like '%B%' then 2 when type like '%C%' then 3 end=a.x and type <=a.type),case when type like '%A%' then 1 when type like '%B%' then 2 when type like '%C%' then 3 end
declare @sql nvarchar(200)
set @sql='select * from tb where type like %'+@TYPE+'%'print @sql
EXEC(@sql)
SELECT *
FROM tb
WHERE type LIKE '%A%'
OR type LIKE '%B%'
OR type LIKE '%C%'
ORDER BY CASE WHEN PATINDEX('%A%', type) > 0 THEN 1
WHEN PATINDEX('%B%', type) > 0 THEN 2
WHEN PATINDEX('%C%', type) > 0 THEN 3
END
when type like '%B%' then 2
when type like '%C%' then 3 end x
from tb a where type like '%A%' or type like '%B%' or type like '%C%'
order by
(select count(1) from tb where (type like '%A%' or type like '%B%' or type like '%C%') and case when type like '%A%' then 1
when type like '%B%' then 2
when type like '%C%' then 3 end=a.x and type <=a.type),case when type like '%A%' then 1
when type like '%B%' then 2
when type like '%C%' then 3 end