Select * from tb where patindex('[^#]%.doc',info) > 0
------------------------------------ -- Author: happyflystone -- Version:V1.001 -- Date:2008-09-12 17:18:09 -------------------------------------- Test Data: tb If object_id('tb') is not null Drop table tb Go Create table tb(info nvarchar(14)) Go Insert into tb select '#cccKKdocf' union all select 'dddoc.fff.ll' union all select 'dafa.doc.hello' union all select '#yuced' union all select '#yuced.doc' union all select 'helldet.doc' union all select 'hadfasd.doc' Go --Start Select * from tb where info like '%.doc' and info not like '#%'Select * from tb where patindex('[^#]%.doc',info) > 0--Result: /*info -------------- helldet.doc hadfasd.doc(所影响的行数为 2 行)info -------------- helldet.doc hadfasd.doc(所影响的行数为 2 行) */ --End
select info from tab where info like '%.doc' and left(info,1)<>'#'
海阔天空你的语句,没有满足 第三个条件 dafa.doc.hello 将显示在结果中
select info from tab where info like '%right(info,4)' and left(info,1)<>'#'
declare @t table (col nvarchar(50)) insert into @t select 'dafa.doc.hello' union all select '#yuced' union all select 'helldet.doc' select col from @t where col like '%.doc' and col not like '#%'
create table mytable (info varchar(20)) go insert into mytable (info) select '#cccKKdocf' union all select 'dddoc.fff.ll' union all select 'dafa.doc.hello' union all select '#yuced' union all select 'helldet.doc' union all select 'hadfasd.doc' select * from mytable where left(info,1)<>'#' and right(rtrim(info),4)='.doc'
from tb
where patindex('[^#]%.doc',info) > 0
-- Author: happyflystone
-- Version:V1.001
-- Date:2008-09-12 17:18:09
-------------------------------------- Test Data: tb
If object_id('tb') is not null
Drop table tb
Go
Create table tb(info nvarchar(14))
Go
Insert into tb
select '#cccKKdocf' union all
select 'dddoc.fff.ll' union all
select 'dafa.doc.hello' union all
select '#yuced' union all
select '#yuced.doc' union all
select 'helldet.doc' union all
select 'hadfasd.doc'
Go
--Start
Select *
from tb
where info like '%.doc' and info not like '#%'Select *
from tb
where patindex('[^#]%.doc',info) > 0--Result:
/*info
--------------
helldet.doc
hadfasd.doc(所影响的行数为 2 行)info
--------------
helldet.doc
hadfasd.doc(所影响的行数为 2 行)
*/
--End
select info from tab
where info like '%.doc' and left(info,1)<>'#'
dafa.doc.hello
将显示在结果中
where info like '%right(info,4)' and left(info,1)<>'#'
declare @t table (col nvarchar(50))
insert into @t
select 'dafa.doc.hello' union all
select '#yuced' union all
select 'helldet.doc' select col
from @t
where col like '%.doc' and col not like '#%'
go
insert into mytable (info)
select '#cccKKdocf'
union all select 'dddoc.fff.ll'
union all select 'dafa.doc.hello'
union all select '#yuced'
union all select 'helldet.doc'
union all select 'hadfasd.doc' select *
from mytable
where left(info,1)<>'#' and right(rtrim(info),4)='.doc'