create table tb(a varchar(10)) insert tb values('ABCDEFG') insert tb values(' ABCD') insert tb values(' ACDEF') insert tb values(' CDAE') insert tb values(' CADE') insert tb values(' ACC') insert tb values(' BCAA') insert tb values(' BCDD')select * from tb WHERE a like '%A%' order by left(ltrim(a),1) drop table tb--运行结果 a ---------- ABCDEFG ABCD ACDEF ACC BCAA CDAE CADE
select * from tb WHERE a like '%A%' order by charindex('A',a)
干脆按照A出现的位置排列:select * from 表 WHERE 字段 like '%A%' order by CHARINDEX('A',rtrim((ltrim(字段))))
--测试 select * from ( select 'ABCDEFG' as a union all select 'ABCD' union all select 'ACDEF' union all select 'CDAE' union all select 'CADE' union all select 'ACC' union all select 'BCAA' ) as a WHERE a like '%A%' order by charindex('A',a),a/* 结果 a ------- ABCD ABCDEFG ACC ACDEF CADE BCAA CDAE(所影响的行数为 7 行) */
直接order by 你的字段就可以了
select * from table where a like '%A%' order by charindex('A',a)
order by left(字段)
insert tb values('ABCDEFG')
insert tb values(' ABCD')
insert tb values(' ACDEF')
insert tb values(' CDAE')
insert tb values(' CADE')
insert tb values(' ACC')
insert tb values(' BCAA')
insert tb values(' BCDD')select * from tb
WHERE a like '%A%'
order by left(ltrim(a),1)
drop table tb--运行结果
a
----------
ABCDEFG
ABCD
ACDEF
ACC
BCAA
CDAE
CADE
WHERE a like '%A%'
order by charindex('A',a)
WHERE 字段 like '%A%'
order by CHARINDEX('A',rtrim((ltrim(字段))))
select * from (
select 'ABCDEFG' as a
union all select
'ABCD'
union all select
'ACDEF'
union all select
'CDAE'
union all select
'CADE'
union all select
'ACC'
union all select
'BCAA'
) as a
WHERE a like '%A%'
order by charindex('A',a),a/*
结果
a
-------
ABCD
ABCDEFG
ACC
ACDEF
CADE
BCAA
CDAE(所影响的行数为 7 行)
*/
where a like '%A%'
order by charindex('A',a)