举例:用like找以下字符查找必须包含abcdef字符串
它前面一个字母必须是=
=前面的2个字母不能同时为ab
它后面必须是&或者为空例如:
Xd=abcdef&
Xd=abcdef
Xq=abcdef&
Xq=abcdef
ac=abcdef&
ac=abcdef
cb=abcdef&
cb=abcdef
但是
ab=abcdef& 和 ab=abcdef 这2个要排除
如果同时含有2个数据,不要排除,例如 Xd=abcdef&ab=abcdef,这个就不要排除了,符合条件!以上用like语句,怎么写?
它前面一个字母必须是=
=前面的2个字母不能同时为ab
它后面必须是&或者为空例如:
Xd=abcdef&
Xd=abcdef
Xq=abcdef&
Xq=abcdef
ac=abcdef&
ac=abcdef
cb=abcdef&
cb=abcdef
但是
ab=abcdef& 和 ab=abcdef 这2个要排除
如果同时含有2个数据,不要排除,例如 Xd=abcdef&ab=abcdef,这个就不要排除了,符合条件!以上用like语句,怎么写?
select * from tb where left(col,2)!='ab' and (charindex('=abcdef',col)>0 or charindex('=abcdef&',col)>0 )
??
UNION ALL
SLECT * FROM TB WHERE COL LIKE '%=ABCDEF&%' AND LEFT(COL,2)<>'AB'
if object_id('tb')is not null drop table tb
go
create table tb( id int identity(1,1),col varchar(119))
insert tb select
'Xd=abcdef&' union all select
'Xd=abcdef' union all select
'Xq=abcdef&' union all select
'Xq=abcdef' union all select
'ac=abcdef&' union all select
'ac=abcdef' union all select
'cb=abcdef&' union all select
'cb=abcdef' union all select
'ab=abcdef&' union all select
'ab=abcdef' union all select
'Xd=abcdef&ab=abcdef'select * from tb
where left(col,2)!='ab' and
(charindex('=abcdef',col)>0 or charindex('=abcdef&',col)>0 )id col
----------- -----------------------------------------------------------------------------------------------------------------------
1 Xd=abcdef&
2 Xd=abcdef
3 Xq=abcdef&
4 Xq=abcdef
5 ac=abcdef&
6 ac=abcdef
7 cb=abcdef&
8 cb=abcdef
11 Xd=abcdef&ab=abcdef(9 行受影响)
SELECT * FROM TB WHERE COL LIKE '%=ABCDEF' AND LEFT(COL,2)<>'AB'
UNION ALL
SELECT * FROM TB WHERE COL LIKE '%=ABCDEF&%' AND LEFT(COL,2)<>'AB'
比如:aaabbbcccdddd=abcdef&aabbccddefsf
SELECT * FROM TB WHERE COL LIKE '%=ABCDEF' AND REVERSE(LEFT(REVERSE(COL),2))<>'AB'
UNION ALL
SELECT * FROM TB WHERE COL LIKE '%=ABCDEF&%' AND REVERSE(LEFT(REVERSE(COL),2))<>'AB'
declare @t table ([cl] varchar(30))
insert into @t
select 'Xd=abcdef&' union all
select 'Xd=abcdef' union all
select 'Xq=abcdef&' union all
select 'Xq=abcdef' union all
select 'ac=abcdef&' union all
select 'ac=abcdef' union all
select 'cb=abcdef&' union all
select 'Xd=abcdef&ab=abcdef' union all
select 'ab=abcdef&' union all
select 'cB=abcdef 'select * from @t
where cl like '%[b-z][a-z]=abcdef[& ]%'
or cl like '%[a-z][a|c-Z]=abcdef[& ]%'cl
------------------------------
Xd=abcdef&
Xq=abcdef&
ac=abcdef&
cb=abcdef&
Xd=abcdef&ab=abcdef
cB=abcdef