关于SQL通配符的使用

[malaluka]

比如我们都知道的：

select name 
    from tablename 
where name like 'a[^1,2,3]c'

表示的是选取name字段中name值不包含a1c，a2c，a3c的值，但是表示的都是单字符


如果我希望搜索的是ac中间不能包含bd,em,xls等多字符时，应该如何操作呢，求大神解答。。。谢谢

[吉普赛的歌]

DECLARE @t TABLE (
	[name] NVARCHAR(100)	
)
INSERT INTO @t VALUES ('axxxc')
INSERT INTO @t VALUES ('abdc')
INSERT INTO @t VALUES ('aemc')
INSERT INTO @t VALUES ('axlsc')

;WITH cte AS(
	SELECT 'bd' AS flag
	UNION ALL
	SELECT 'em' AS flag
	UNION ALL
	SELECT 'xls' AS flag
)
SELECT * FROM @t AS a 
WHERE a.[name] LIKE 'a%c'AND NOT EXISTS(
	SELECT 1 FROM cte AS b WHERE a.[name] = 'a'+b.flag+'c'
)
/*
name
-----
axxxc
*/

	

[Dear SQL(燊)]

也可以用left join ,在参数表（如下：para）

;
with datas as(
	select [name]='a123c' union all
	select [name]='aedc' union all
	select [name]='axxc' union all
	select [name]='a32c' union all
	select [name]='axjc' 
),para as(
	select mark='aedc' union  all
	select mark='a32c'

)
select a.*
from datas a
left join para b on a.[name]=b.mark
where b.mark is null

name
-----
a123c
axxc
axjc

[eaglexie]

学习一下，这个方便多了

[Hayley_244]

感谢分享！写得很详细！