select * from aa where charindex('/科幻/','/'+字段+'/')>0
select * from tb where charindex('/'+'科幻'+'/','/'+colname+'/')>0
select * from aa where charindex('/科幻/','/'+字段+'/')>0
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB] create table [TB]([字段] varchar(15)) insert [TB] select '科幻片/动作片' union all select '科幻/惊悚片' union all select '战争片/科幻/XXX' union all select 'XXX/XXXX/科幻' union all select 'XXX/科幻片/XXX' union all select 'XXX/XXX/科幻片'select * from [TB] where 字段 like '%科幻/%' /* 字段 --------------- 科幻/惊悚片 战争片/科幻/XXX(所影响的行数为 2 行)*/ drop table TB
select * from [TB] where 字段+'/' like '%科幻/%'
--> 测试数据:@tb declare @tb table([字段] varchar(15)) insert @tb select '科幻片/动作片' union all select '科幻/惊悚片' union all select '战争片/科幻/XXX' union all select 'XXX/XXXX/科幻' union all select 'XXX/科幻片/XXX' union all select 'XXX/XXX/科幻片'select * from @tb where charindex('/科幻/','/'+字段+'/')>0 /*字段 --------------- 科幻/惊悚片 战争片/科幻/XXX XXX/XXXX/科幻 */
charindex('科幻',col)>0 and charindex('科幻片',col)<=0
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-28 15:06:57 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([字段] varchar(15)) insert [tb] select '科幻片/动作片' union all select '科幻/惊悚片' union all select '战争片/科幻/XXX' union all select 'XXX/XXXX/科幻' union all select 'XXX/科幻片/XXX' union all select 'XXX/XXX/科幻片' --------------开始查询-------------------------- select * from tb where charindex('/'+'科幻'+'/','/'+字段+'/')>0 ----------------结果---------------------------- /* 字段 --------------- 科幻/惊悚片 战争片/科幻/XXX XXX/XXXX/科幻(3 行受影响)*/
create table tb([name] nvarchar(20)) goinsert into tb select '科幻片/动作片' union all select '科幻/惊悚片' union all select '战争片/科幻/XXX' union all select 'XXX/XXXX/科幻' union all select 'XXX/科幻片/XXX' union all select 'XXX/XXX/科幻片' select * from tb where [name]+' ' like '%科幻[^片]%' /*name -------------------- 科幻/惊悚片 战争片/科幻/XXX XXX/XXXX/科幻(所影响的行数为 3 行) */drop table tb
select * from aa
where charindex('/科幻/','/'+字段+'/')>0
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([字段] varchar(15))
insert [TB]
select '科幻片/动作片' union all
select '科幻/惊悚片' union all
select '战争片/科幻/XXX' union all
select 'XXX/XXXX/科幻' union all
select 'XXX/科幻片/XXX' union all
select 'XXX/XXX/科幻片'select * from [TB] where 字段 like '%科幻/%'
/*
字段
---------------
科幻/惊悚片
战争片/科幻/XXX(所影响的行数为 2 行)*/
drop table TB
declare @tb table([字段] varchar(15))
insert @tb
select '科幻片/动作片' union all
select '科幻/惊悚片' union all
select '战争片/科幻/XXX' union all
select 'XXX/XXXX/科幻' union all
select 'XXX/科幻片/XXX' union all
select 'XXX/XXX/科幻片'select * from @tb where charindex('/科幻/','/'+字段+'/')>0
/*字段
---------------
科幻/惊悚片
战争片/科幻/XXX
XXX/XXXX/科幻
*/
charindex('科幻',col)>0 and charindex('科幻片',col)<=0
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-28 15:06:57
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段] varchar(15))
insert [tb]
select '科幻片/动作片' union all
select '科幻/惊悚片' union all
select '战争片/科幻/XXX' union all
select 'XXX/XXXX/科幻' union all
select 'XXX/科幻片/XXX' union all
select 'XXX/XXX/科幻片'
--------------开始查询--------------------------
select * from tb where charindex('/'+'科幻'+'/','/'+字段+'/')>0
----------------结果----------------------------
/* 字段
---------------
科幻/惊悚片
战争片/科幻/XXX
XXX/XXXX/科幻(3 行受影响)*/
goinsert into tb select
'科幻片/动作片' union all select
'科幻/惊悚片' union all select
'战争片/科幻/XXX' union all select
'XXX/XXXX/科幻' union all select
'XXX/科幻片/XXX' union all select
'XXX/XXX/科幻片'
select * from tb where [name]+' ' like '%科幻[^片]%'
/*name
--------------------
科幻/惊悚片
战争片/科幻/XXX
XXX/XXXX/科幻(所影响的行数为 3 行)
*/drop table tb