if object_id('tb') is not null drop table tb go create table tb (id int identity(1,1) primary key,col2 varchar(10)) go insert into tb select 'npexa' union all select 'npexabb' union all select 'npe xabg' union all select 'np e xagh' union all select 'n pe xabf' go select * from tb where col2 like 'npexa%' /*id col2 ----------- ---------- 1 npexa 2 npexabb(2 行受影响)*/ select * from tb where col2 like 'npe%xa%' /*id col2 ----------- ---------- 1 npexa 2 npexabb 3 npe xabg(3 行受影响) */ select * from tb where col2 like 'np%e%xa%' /*id col2 ----------- ---------- 1 npexa 2 npexabb 3 npe xabg 4 np e xagh(4 行受影响)*/
CREATE TABLE TBTEST([KEY] VARCHAR(20)) INSERT TBTEST SELECT 'npexaREW' UNION SELECT 'npe xa REW' UNION SELECT '543'select * from TBTEST where [key] like 'npe%xa%' KEY -------------------- npe xa REW npexaREW(所影响的行数为 2 行)
但是如果npexa中间有空格的话,就不能出来值select * from test where key like 'npe xa%' 这该怎么解决?把空格去掉...
别用 LIKE了。 LIKE '%npe' 如果%开头 就用不到索引了 select * from TBTEST where charindex('npe xa',key)>0
if object_id('tb') is not null drop table tb go create table tb (id int identity(1,1) primary key,col2 varchar(10)) go insert into tb select 'npexa' union all select 'npexabb' union all select 'npe xabg' union all select 'np e xagh' union all select 'n pe xabf' go select * from tb where REPLACE(col2,' ','') like'npexa%'/*id col2 ----------- ---------- 1 npexa 2 npexabb 3 npe xabg 4 np e xagh 5 n pe xabf(5 行受影响)*/
select * from tb where REPLACE(col2,' ','') like'npexa%'
drop table tb
go
create table tb (id int identity(1,1) primary key,col2 varchar(10))
go
insert into tb select 'npexa'
union all select 'npexabb'
union all select 'npe xabg'
union all select 'np e xagh'
union all select 'n pe xabf'
go
select * from tb where col2 like 'npexa%'
/*id col2
----------- ----------
1 npexa
2 npexabb(2 行受影响)*/
select * from tb where col2 like 'npe%xa%'
/*id col2
----------- ----------
1 npexa
2 npexabb
3 npe xabg(3 行受影响)
*/
select * from tb where col2 like 'np%e%xa%'
/*id col2
----------- ----------
1 npexa
2 npexabb
3 npe xabg
4 np e xagh(4 行受影响)*/
INSERT TBTEST
SELECT 'npexaREW' UNION
SELECT 'npe xa REW' UNION
SELECT '543'select * from TBTEST where [key] like 'npe%xa%' KEY
--------------------
npe xa REW
npexaREW(所影响的行数为 2 行)
这该怎么解决?把空格去掉...
LIKE '%npe' 如果%开头 就用不到索引了
select * from TBTEST where charindex('npe xa',key)>0
if object_id('tb') is not null
drop table tb
go
create table tb (id int identity(1,1) primary key,col2 varchar(10))
go
insert into tb select 'npexa'
union all select 'npexabb'
union all select 'npe xabg'
union all select 'np e xagh'
union all select 'n pe xabf'
go
select * from tb where REPLACE(col2,' ','') like'npexa%'/*id col2
----------- ----------
1 npexa
2 npexabb
3 npe xabg
4 np e xagh
5 n pe xabf(5 行受影响)*/