declare @test1 table([ID] int,[NAME] nvarchar(4),[SNUMBER] nvarchar(7),[LIST] nvarchar(4)) Insert @test1 select 1,N'JOIN',N'001_901',N'90%' union all select 2,N'TONY',N'001_909',N'99%' union all select 3,N'LING',N'001',N'100' Select * from @test1 where charindex('%',[LIST])>0 /* ID NAME SNUMBER LIST ----------- ---- ------- ---- 1 JOIN 001_901 90% 2 TONY 001_909 99%*/ Select * from @test1 where charindex('_',[SNUMBER])>0 /* ID NAME SNUMBER LIST ----------- ---- ------- ---- 1 JOIN 001_901 90% 2 TONY 001_909 99%*/
select * from test1 where snumber like '%_%'
--try create table test ( id int, name varchar(10), snumber varchar(20), list varchar(10) ) insert into test select 1, 'JOIN', '001_901', '90%' union select 2, 'TONY', '001_909', '99%' union select 3, 'LING', '001 ', '100%' select * from test where charindex('_',snumber) > 0id name snumber list ----------- ---------- -------------------- ---------- 1 JOIN 001_901 90% 2 TONY 001_909 99%(2 row(s) affected)
只能这样吗?? 难道SQL 2000没有对含通配符的内容过滤是有什么更好的方法???
declare @t table(no varchar(10),col varchar(1)) insert into @T select '#08020001' , 'A' insert into @T select '#08020002' , 'B' insert into @T select '#%08010001', 'C' insert into @T select '#%08010002', 'D' insert into @T select 'PO08010001', 'E' insert into @T select 'SO08010002', 'F'select * from @t where no like '#$%%' escape '$'select * from @t where no like '#%' and no not like '#$%%' escape '$'/* no col ---------- ---- #%08010001 C #%08010002 D(2 行受影响)no col ---------- ---- #08020001 A #08020002 B(2 行受影响)*/
select * from 表 where snumber like '%[_]%' 用[]易于组合,条件范围较大。
declare @test1 table([ID] int,[NAME] nvarchar(4),[SNUMBER] nvarchar(7),[LIST] nvarchar(4)) Insert @test1 select 1,N'JOIN',N'001_901',N'90%' union all select 2,N'TONY',N'001_909',N'99%' union all select 3,N'LING',N'001',N'100'select * from @test1 where snumber like '%[_]%' select char(95)
使用 ESCAPE 关键字定义转义符。在模式中,当转义符置于通配符之前时,该通配符就解释为普通字符。例如,要搜索在任意位置包含字符串 5% 的字符串,请使用: <sql> declare @test1 table([ID] int,[NAME] nvarchar(4),[SNUMBER] nvarchar(7),[LIST] nvarchar(4)) Insert @test1 select 1,N'JOIN',N'001_901',N'90%' union all select 2,N'TONY',N'001_909',N'99%' union all select 3,N'LING',N'001%',N'100'select * from @test1 where snumber like '%/_%' ESCAPE '/' select * from @test1 where snumber like '%/%%' ESCAPE '/' </sql>
奇怪,FF竟然不支持插入源码==>your browser is not support range/* 使用 ESCAPE 关键字定义转义符。在模式中,当转义符置于通配符之前时,该通配符就解释为普通字符。例如,要搜索在任意位置包含字符串 5% 的字符串,请使用: */ declare @test1 table([ID] int,[NAME] nvarchar(4),[SNUMBER] nvarchar(7),[LIST] nvarchar(4)) Insert @test1 select 1,N'JOIN',N'001_901',N'90%' union all select 2,N'TONY',N'001_909',N'99%' union all select 3,N'LING',N'001%',N'100'select * from @test1 where snumber like '%/_%' ESCAPE '/' select * from @test1 where snumber like '%/%%' ESCAPE '/'
--> --> (Andy)生成测试数据 2008-06-20 21:06:06 Set Nocount On declare @1 table([ID] int,[NAME] nvarchar(4),[SNUMBER] nvarchar(7),[LIST] nvarchar(4)) Insert @1 select 1,N'JOIN',N'001_901',N'90%' union all select 2,N'TONY',N'001_909',N'99%' union all select 3,N'LING',N'001',N'100%'
Select * from @1 Where Patindex('%[_]%',[SNUMBER])>0 Select * from @1 Where Charindex('_',[SNUMBER])>0 Select * from @1 Where [SNUMBER] Like '%[_]%'/* ID NAME SNUMBER LIST ----------- ---- ------- ---- 1 JOIN 001_901 90% 2 TONY 001_909 99%ID NAME SNUMBER LIST ----------- ---- ------- ---- 1 JOIN 001_901 90% 2 TONY 001_909 99%ID NAME SNUMBER LIST ----------- ---- ------- ---- 1 JOIN 001_901 90% 2 TONY 001_909 99% */
--> 测试数据: [test1] if object_id('[test1]') is not null drop table [test1] create table [test1] (ID int,NAME varchar(4),SNUMBER varchar(7),LIST varchar(4)) insert into [test1] select 1,'JOIN','001_901','90%' union all select 2,'TONY','001_909','99%' union all select 3,'LING','001','100%'select * from [test1] where charindex('_',SNUMBER)>0 /* 1 JOIN 001_901 90% 2 TONY 001_909 99% */ select * from [test1] where SNUMBER like '%[_]%' /* 1 JOIN 001_901 90% 2 TONY 001_909 99% */ select * from [test1] where SNUMBER like '%/_%' escape '/' /* 1 JOIN 001_901 90% 2 TONY 001_909 99% */
要那么复杂吗?直接SELECT * FROM TESTS WHERE LEN(SNUMBER) >3
select * from test1 where snumber like '%_%'
select * from [test1] where SNUMBER like '%/_%' escape '/' 使用escape 直接.
select * from test1 where sumber like '%_%'
select * from [test1] where charindex('_',SNUMBER)>0select * from [test1] where SNUMBER like '%[_]%'select * from [test1] where patindex('%[_]%',SNUMBER)>0
谢谢大家。 最完整的解答看6楼的同志回答。 SQL Like 通配符特殊用法 escape (MS-SQL Server) http://www.sqlstudy.com/sql_article.php?id=2008061601
declare @test1 table([ID] int,[NAME] nvarchar(4),[SNUMBER] nvarchar(7),[LIST] nvarchar(4))
Insert @test1
select 1,N'JOIN',N'001_901',N'90%' union all
select 2,N'TONY',N'001_909',N'99%' union all
select 3,N'LING',N'001',N'100'
Select * from @test1 where charindex('%',[LIST])>0
/*
ID NAME SNUMBER LIST
----------- ---- ------- ----
1 JOIN 001_901 90%
2 TONY 001_909 99%*/
Select * from @test1 where charindex('_',[SNUMBER])>0
/*
ID NAME SNUMBER LIST
----------- ---- ------- ----
1 JOIN 001_901 90%
2 TONY 001_909 99%*/
where snumber like '%_%'
create table test
(
id int,
name varchar(10),
snumber varchar(20),
list varchar(10)
)
insert into test
select
1, 'JOIN', '001_901', '90%' union select
2, 'TONY', '001_909', '99%' union select
3, 'LING', '001 ', '100%'
select *
from test
where charindex('_',snumber) > 0id name snumber list
----------- ---------- -------------------- ----------
1 JOIN 001_901 90%
2 TONY 001_909 99%(2 row(s) affected)
难道SQL 2000没有对含通配符的内容过滤是有什么更好的方法???
insert into @T select '#08020001' , 'A'
insert into @T select '#08020002' , 'B'
insert into @T select '#%08010001', 'C'
insert into @T select '#%08010002', 'D'
insert into @T select 'PO08010001', 'E'
insert into @T select 'SO08010002', 'F'select * from @t where no like '#$%%' escape '$'select * from @t where no like '#%' and no not like '#$%%' escape '$'/*
no col
---------- ----
#%08010001 C
#%08010002 D(2 行受影响)no col
---------- ----
#08020001 A
#08020002 B(2 行受影响)*/
http://www.sqlstudy.com/sql_article.php?id=2008061601
用[]易于组合,条件范围较大。
Insert @test1
select 1,N'JOIN',N'001_901',N'90%' union all
select 2,N'TONY',N'001_909',N'99%' union all
select 3,N'LING',N'001',N'100'select * from @test1 where snumber like '%[_]%'
select char(95)
<sql>
declare @test1 table([ID] int,[NAME] nvarchar(4),[SNUMBER] nvarchar(7),[LIST] nvarchar(4))
Insert @test1
select 1,N'JOIN',N'001_901',N'90%' union all
select 2,N'TONY',N'001_909',N'99%' union all
select 3,N'LING',N'001%',N'100'select * from @test1 where snumber like '%/_%' ESCAPE '/'
select * from @test1 where snumber like '%/%%' ESCAPE '/'
</sql>
使用 ESCAPE 关键字定义转义符。在模式中,当转义符置于通配符之前时,该通配符就解释为普通字符。例如,要搜索在任意位置包含字符串 5% 的字符串,请使用:
*/
declare @test1 table([ID] int,[NAME] nvarchar(4),[SNUMBER] nvarchar(7),[LIST] nvarchar(4))
Insert @test1
select 1,N'JOIN',N'001_901',N'90%' union all
select 2,N'TONY',N'001_909',N'99%' union all
select 3,N'LING',N'001%',N'100'select * from @test1 where snumber like '%/_%' ESCAPE '/'
select * from @test1 where snumber like '%/%%' ESCAPE '/'
Set Nocount On
declare @1 table([ID] int,[NAME] nvarchar(4),[SNUMBER] nvarchar(7),[LIST] nvarchar(4))
Insert @1
select 1,N'JOIN',N'001_901',N'90%' union all
select 2,N'TONY',N'001_909',N'99%' union all
select 3,N'LING',N'001',N'100%'
Select * from @1 Where Patindex('%[_]%',[SNUMBER])>0
Select * from @1 Where Charindex('_',[SNUMBER])>0
Select * from @1 Where [SNUMBER] Like '%[_]%'/*
ID NAME SNUMBER LIST
----------- ---- ------- ----
1 JOIN 001_901 90%
2 TONY 001_909 99%ID NAME SNUMBER LIST
----------- ---- ------- ----
1 JOIN 001_901 90%
2 TONY 001_909 99%ID NAME SNUMBER LIST
----------- ---- ------- ----
1 JOIN 001_901 90%
2 TONY 001_909 99%
*/
if object_id('[test1]') is not null drop table [test1]
create table [test1] (ID int,NAME varchar(4),SNUMBER varchar(7),LIST varchar(4))
insert into [test1]
select 1,'JOIN','001_901','90%' union all
select 2,'TONY','001_909','99%' union all
select 3,'LING','001','100%'select * from [test1] where charindex('_',SNUMBER)>0
/*
1 JOIN 001_901 90%
2 TONY 001_909 99%
*/
select * from [test1] where SNUMBER like '%[_]%'
/*
1 JOIN 001_901 90%
2 TONY 001_909 99%
*/
select * from [test1] where SNUMBER like '%/_%' escape '/'
/*
1 JOIN 001_901 90%
2 TONY 001_909 99%
*/
where snumber like '%_%'
使用escape 直接.
最完整的解答看6楼的同志回答。
SQL Like 通配符特殊用法 escape (MS-SQL Server)
http://www.sqlstudy.com/sql_article.php?id=2008061601