查询语句如下:
select name from table where name like '%南%'
如何对查询的结果排序达到以下效果:
关键字“南”出现在name中第一位的排在前,接着排“南”出现在name中第二位,以此类推,排完所有查询结果。
请问如何实现?
select name from table where name like '%南%'
如何对查询的结果排序达到以下效果:
关键字“南”出现在name中第一位的排在前,接着排“南”出现在name中第二位,以此类推,排完所有查询结果。
请问如何实现?
from tb
where name like '%南%'
order by charindex('南',name)
*
from
tb
where
charindex('南',name)>0
order by
charindex('南',name)
declare @a table(name varchar(50))
insert @a select
'd南sdg' union all select
'ad南sdg' union all select
'b南sdg' union all select
'gd南sdg' union all select
'ggd南sdg' union all select
'fffd南sdg' union all select
'ffd南sdg' select * from @a ORDER BY charindex('南',name)
name
--------------------------------------------------
b南sdg
d南sdg
ad南sdg
gd南sdg
ggd南sdg
ffd南sdg
fffd南sdg(7 行受影响)
where name like '%南%'
order by PATINDEX('%南%',name)
insert @a select
'd南sdg' union all select
'ad南sdg' union all select
'b南sdg' union all select
'gd南sdg' union all select
'ggd南sdg' union all select
'fffd南sdg' union all select
'ffd南sdg' select * from @A
where name like '%南%'
order by PATINDEX('%南%',name)(所影响的行数为 7 行)name
--------------------------------------------------
d南sdg
b南sdg
gd南sdg
ad南sdg
ggd南sdg
ffd南sdg
fffd南sdg(所影响的行数为 7 行)