where ... like '%'+'1,2,3,4,5,6'+'%' ========================================================================= 我想我是没有说明白把"1,2,3,4,5,6"是不确定的字符串~
---------------------------------------- 直接把 '1,2,3,4,5,6' 替换成你的不确定的字符串比如 where ... like '%'+@a+'%' ================================================================================= 呵呵,查不到结果
回复人:Tigersong(宋) ( 二级(初级)) 信誉:100 where ... like '%'+'1,2,3,4,5,6'+'%'-------------------------------------'1,2,4,5,6' like '%'+'1,2,3,4,5,6'+'%' 吗? 不like吧?
declare @s1 as varchar(100) declare @s2 as varchar(100) set @s1 = '1,2,3,4,5,6' set @s2 = '1,2,4,5,6' if charindex(',' + @s2 + ',' , ',' + @s1 + ',') > 0 print '能查到' else print '查不到'
ALTER Proc MoreConditionSlect @Habitation varchar(100),@Sex char(1),@Eduction varchar(10), @WorkTime varchar(20),@NowJobtype varchar(50), @NowJobName varchar(50),@ISManage char(1), @Area varchar(30),@Trade varchar (50),@RefurbishTime DateTime, @Language varchar(20),@LS varchar(10),@StartTime DateTime,@EndTime DateTime,@Company varchar(50) AS select UB_UserName,UB_Tname,UB_Brithday,UB_Habitation,UB_SEX,UB_Eduction,UB_WorkTime,UJ_NowJobtype,UEI_Company,UJ_Nowtrade,UJ_NowJobName,UJ_ISManage,UJ_Area,UJ_Trade,UR_RefurbishTime,ULI_Language,ULI_LS from (select a.*,b.*,c.*,d.*,e.* from dbo.U_Base_Info as a, dbo.U_Job_Intent as b,dbo.U_Resume_Manage as c,dbo.U_Lang_Info as d,dbo.U_Experience_Info as e where a.UB_UserName = b.UJ_UserName and b.UJ_UserName = c.UR_BelongUser and c.UR_BelongUser = d.ULI_UserName and d.ULI_UserName = e.UEI_UserName and e.UEI_Btime in (select max(UEI_Btime) from dbo.U_Experience_Info group by dbo.U_Experience_Info.UEI_UserName ) ) as Exp1 where ( UB_Habitation like '%'+@Habitation+'%' or @Habitation = '' ) and ( UB_SEX like '%'+@Sex+'%' or @Sex = '' ) and ( UB_Eduction like '%'+@Eduction+'%' or @Eduction = '' ) and ( UB_WorkTime like '%'+@WorkTime+'%' or @WorkTime = '' ) and ( UJ_NowJobtype like '%'+@NowJobtype+'%' or @NowJobtype = '' ) and ( UJ_NowJobName like '%'+@NowJobName+'%' or @NowJobName = '' ) and ( UJ_ISManage like '%'+@ISManage+'%' or @ISManage = '' ) and ( (set @Language = replace(@Language,',','%') set @Language = '%' + @Language + '%') patindex (@Language,ULI_Language)) or ULI_Language = '' ) and ( UJ_Area like '%'+@Area+'%' or @Area = '' ) and ( UJ_Trade like '%'+@Trade+'%' or @Trade = '' ) and ( UR_RefurbishTime <= @RefurbishTime or @RefurbishTime = null ) and ( ULI_LS like '%'+@LS+'%' or @LS = '' ) and ( UJ_Trade like '%'+@Trade+'%' or @Trade ='' ) and ( (UB_Brithday >= @StartTime and UB_Brithday <= @EndTime) or (@StartTime = null and @EndTime = null) ) and ( UEI_Company like '%'+@Company+'%' or @Company = '' ) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO 服务器: 消息 156,级别 15,状态 1,过程 MoreConditionSlect,行 16 在关键字 'set' 附近有语法错误。 服务器: 消息 170,级别 15,状态 1,过程 MoreConditionSlect,行 17 第 17 行: ')' 附近有语法错误。
/* --在 where 里能“set”吗? */ ALTER Proc MoreConditionSlect @Habitation varchar(100),@Sex char(1),@Eduction varchar(10), @WorkTime varchar(20),@NowJobtype varchar(50), @NowJobName varchar(50),@ISManage char(1), @Area varchar(30),@Trade varchar (50),@RefurbishTime DateTime, @Language varchar(20),@LS varchar(10),@StartTime DateTime,@EndTime DateTime,@Company varchar(50) ASset @Language = replace(@Language,',','%') set @Language = '%' + @Language + '%'select UB_UserName,UB_Tname,UB_Brithday,UB_Habitation,UB_SEX,UB_Eduction,UB_WorkTime,UJ_NowJobtype,UEI_Company,UJ_Nowtrade,UJ_NowJobName,UJ_ISManage,UJ_Area,UJ_Trade,UR_RefurbishTime,ULI_Language,ULI_LS from (select a.*,b.*,c.*,d.*,e.* from dbo.U_Base_Info as a, dbo.U_Job_Intent as b,dbo.U_Resume_Manage as c,dbo.U_Lang_Info as d,dbo.U_Experience_Info as e where a.UB_UserName = b.UJ_UserName and b.UJ_UserName = c.UR_BelongUser and c.UR_BelongUser = d.ULI_UserName and d.ULI_UserName = e.UEI_UserName and e.UEI_Btime in (select max(UEI_Btime) from dbo.U_Experience_Info group by dbo.U_Experience_Info.UEI_UserName ) ) as Exp1 where ( UB_Habitation like '%'+@Habitation+'%' or @Habitation = '' ) and ( UB_SEX like '%'+@Sex+'%' or @Sex = '' ) and ( UB_Eduction like '%'+@Eduction+'%' or @Eduction = '' ) and ( UB_WorkTime like '%'+@WorkTime+'%' or @WorkTime = '' ) and ( UJ_NowJobtype like '%'+@NowJobtype+'%' or @NowJobtype = '' ) and ( UJ_NowJobName like '%'+@NowJobName+'%' or @NowJobName = '' ) and ( UJ_ISManage like '%'+@ISManage+'%' or @ISManage = '' ) and ( patindex(@Language,ULI_Language)>0 or ULI_Language = '') and ( UJ_Area like '%'+@Area+'%' or @Area = '' ) and ( UJ_Trade like '%'+@Trade+'%' or @Trade = '' ) and ( UR_RefurbishTime <= @RefurbishTime or @RefurbishTime = null ) and ( ULI_LS like '%'+@LS+'%' or @LS = '' ) and ( UJ_Trade like '%'+@Trade+'%' or @Trade ='' ) and ( (UB_Brithday >= @StartTime and UB_Brithday <= @EndTime) or (@StartTime = null and @EndTime = null) ) and ( UEI_Company like '%'+@Company+'%' or @Company = '' ) GO
( patindex(@Language,ULI_Language)>0 or ULI_Language = '') 能实现下面的功能吗?declare @pat varchar(100) set @pat = '1,2,4,5,6'set @pat = replace(@pat,',','%') set @pat = '%'+@pat+'%'if patindex(@pat, '1,2,3,4,5,6')>0 print '查到' else print '查不到'谢谢~
PatIndex('%'+Replace(@Language,',','%')+'%',ULI_Language)>0 ---这样????? -------------------------------------------------------------------------- Create Proc MoreConditionSlect @Habitation varchar(100),@Sex char(1),@Eduction varchar(10), @WorkTime varchar(20),@NowJobtype varchar(50), @NowJobName varchar(50),@ISManage char(1), @Area varchar(30),@Trade varchar (50),@RefurbishTime DateTime, @Language varchar(20),@LS varchar(10),@StartTime DateTime,@EndTime DateTime,@Company varchar(50) AS select UB_UserName,UB_Tname,UB_Brithday,UB_Habitation,UB_SEX,UB_Eduction,UB_WorkTime,UJ_NowJobtype,UEI_Company,UJ_Nowtrade,UJ_NowJobName,UJ_ISManage,UJ_Area,UJ_Trade,UR_RefurbishTime,ULI_Language,ULI_LS from (select a.*,b.*,c.*,d.*,e.* from dbo.U_Base_Info as a, dbo.U_Job_Intent as b,dbo.U_Resume_Manage as c,dbo.U_Lang_Info as d,dbo.U_Experience_Info as e where a.UB_UserName = b.UJ_UserName and b.UJ_UserName = c.UR_BelongUser and c.UR_BelongUser = d.ULI_UserName and d.ULI_UserName = e.UEI_UserName and e.UEI_Btime in (select max(UEI_Btime) from dbo.U_Experience_Info group by dbo.U_Experience_Info.UEI_UserName ) ) as Exp1 where ( UB_Habitation like '%'+@Habitation+'%' or @Habitation = '' ) and ( UB_SEX like '%'+@Sex+'%' or @Sex = '' ) and ( UB_Eduction like '%'+@Eduction+'%' or @Eduction = '' ) and ( UB_WorkTime like '%'+@WorkTime+'%' or @WorkTime = '' ) and ( UJ_NowJobtype like '%'+@NowJobtype+'%' or @NowJobtype = '' ) and ( UJ_NowJobName like '%'+@NowJobName+'%' or @NowJobName = '' ) and ( UJ_ISManage like '%'+@ISManage+'%' or @ISManage = '' ) and ( (PatIndex('%'+Replace(@Language,',','%')+'%',ULI_Language)>0) or ULI_Language = '' ) and ( UJ_Area like '%'+@Area+'%' or @Area = '' ) and ( UJ_Trade like '%'+@Trade+'%' or @Trade = '' ) and ( UR_RefurbishTime <= @RefurbishTime or @RefurbishTime = null ) and ( ULI_LS like '%'+@LS+'%' or @LS = '' ) and ( (UB_Brithday >= @StartTime and UB_Brithday <= @EndTime) or (@StartTime = null and @EndTime = null) ) and ( UEI_Company like '%'+@Company+'%' or @Company = '' ) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
/* 如果@find子串在@field中都出现,返回1,否则返回0。 */ create function fn_exists (@find varchar(1000), @Field varchar(1000)) returns bit as begin declare @return bit set @return=1 --去左右空格 set @find=ltrim(rtrim(@find))+',' while charindex(',',@find)>0 begin if charindex(left(@find,charindex(',',@find)-1),@field)=0 begin set @return=0 break end set @find = right(@find,len(@find)-charindex(',',@find)) end return(@return) end go
/* 如果@find子串在@field中都出现,返回1,否则返回0。 */ create function fn_exists (@find varchar(1000), @Field varchar(1000)) returns bit as begin declare @return bit set @return=1 --去左右空格 set @find=ltrim(rtrim(@find))+',' while charindex(',',@find)>0 begin if charindex(left(@find,charindex(',',@find)-1),@field)=0 begin set @return=0 break end set @find = right(@find,len(@find)-charindex(',',@find)) end return(@return) end go ======================================= 太谢谢了~ 这段能放在存储过程中吗?怎么调用他?
Create Proc MoreConditionSlect @Habitation varchar(100),@Sex char(1),@Eduction varchar(10), @WorkTime varchar(20),@NowJobtype varchar(50), @NowJobName varchar(50),@ISManage char(1), @Area varchar(30),@Trade varchar (50),@RefurbishTime DateTime, @Language varchar(20),@LS varchar(10),@StartTime DateTime,@EndTime DateTime,@Company varchar(50) AS select UB_UserName,UB_Tname,UB_Brithday,UB_Habitation,UB_SEX,UB_Eduction,UB_WorkTime,UJ_NowJobtype,UEI_Company,UJ_Nowtrade,UJ_NowJobName,UJ_ISManage,UJ_Area,UJ_Trade,UR_RefurbishTime,ULI_Language,ULI_LS from (select a.*,b.*,c.*,d.*,e.* from dbo.U_Base_Info as a, dbo.U_Job_Intent as b,dbo.U_Resume_Manage as c,dbo.U_Lang_Info as d,dbo.U_Experience_Info as e where a.UB_UserName = b.UJ_UserName and b.UJ_UserName = c.UR_BelongUser and c.UR_BelongUser = d.ULI_UserName and d.ULI_UserName = e.UEI_UserName and e.UEI_Btime in (select max(UEI_Btime) from dbo.U_Experience_Info group by dbo.U_Experience_Info.UEI_UserName ) ) as Exp1 where ( UB_Habitation like '%'+@Habitation+'%' or @Habitation = '' ) and ( UB_SEX like '%'+@Sex+'%' or @Sex = '' ) and ( UB_Eduction like '%'+@Eduction+'%' or @Eduction = '' ) and ( UB_WorkTime like '%'+@WorkTime+'%' or @WorkTime = '' ) and ( UJ_NowJobtype like '%'+@NowJobtype+'%' or @NowJobtype = '' ) and ( UJ_NowJobName like '%'+@NowJobName+'%' or @NowJobName = '' ) and ( UJ_ISManage like '%'+@ISManage+'%' or @ISManage = '' ) and ( (dbo.fn_exists(@Language,ULI_Language)=1) or ULI_Language = '' ) and ( UJ_Area like '%'+@Area+'%' or @Area = '' ) and ( UJ_Trade like '%'+@Trade+'%' or @Trade = '' ) and ( UR_RefurbishTime <= @RefurbishTime or @RefurbishTime = null ) and ( ULI_LS like '%'+@LS+'%' or @LS = '' ) and ( (UB_Brithday >= @StartTime and UB_Brithday <= @EndTime) or (@StartTime = null and @EndTime = null) ) and ( UEI_Company like '%'+@Company+'%' or @Company = '' ) GO
望各位高手不吝赐教,谢谢~~~~select * from tb where charindex(',' + '1,2,4,5,6' + ',' , ',' + col + ',') > 0
where ... like '%'+'1,2,3,4,5,6'+'%'
=========================================================================
我想我是没有说明白把"1,2,3,4,5,6"是不确定的字符串~
Tigersong(宋) ( ) 信誉:100 2007-09-04 11:19:54 得分: 0
where ... like '%'+'1,2,3,4,5,6'+'%'
=========================================================================
我想我是没有说明白把"1,2,3,4,5,6"是不确定的字符串~
----------------------------------------
直接把 '1,2,3,4,5,6' 替换成你的不确定的字符串比如 where ... like '%'+@a+'%'
按顺序包含么?用"2,1,4,5,6"查能不能得到"1,2,3,4,5,6"?
==================================================================================
能查到~
soft1000_hb() ( ) 信誉:100 2007-09-04 11:21:59 得分: 0
Tigersong(宋) ( ) 信誉:100 2007-09-04 11:19:54 得分: 0
where ... like '%'+'1,2,3,4,5,6'+'%'
=========================================================================
我想我是没有说明白把"1,2,3,4,5,6"是不确定的字符串~
----------------------------------------
直接把 '1,2,3,4,5,6' 替换成你的不确定的字符串比如 where ... like '%'+@a+'%'
=================================================================================
呵呵,查不到结果
where ... like '%'+'1,2,3,4,5,6'+'%'-------------------------------------'1,2,4,5,6' like '%'+'1,2,3,4,5,6'+'%' 吗?
不like吧?
declare @s2 as varchar(100)
set @s1 = '1,2,3,4,5,6'
set @s2 = '1,2,4,5,6' if charindex(',' + @s2 + ',' , ',' + @s1 + ',') > 0
print '能查到'
else
print '查不到'
set @s2 = '1,2,4,5,6'if charindex(',' + @s2 + ',' , ',' + @s1 + ',') > 0 ---------------------这样charindex(',' + @s2 + ',' , ',' + @s1 + ',')永远等于0
declare @s1 as varchar(100)
declare @s2 as varchar(100)
set @s1 = '1,2,3,4,5,6'
set @s2 = '1,2,4,5,6' if charindex(',' + @s2 + ',' , ',' + @s1 + ',') > 0
print '能查到'
else
print '查不到'
==================================================================
谢谢,@s2可能不包含','如@s2='1',也可能为空
望各位高手不吝赐教,谢谢~~~~------------------------------------------
想都不用想,这肯定要patindex+通配符来模糊匹配,
查询字符串"1,2,4,5,6"要变形,如果查询字符串时字段,那就写个函数让他变形:declare @pat varchar(100)
set@pat = '1,2,4,5,6'
set @pat = replace(@pat,',','%')
set @pat = '%'+@pat+'%'if patindex(@pat, '1,2,3,4,5,6')>0
print '查到'
else
print '查不到'
--set@pat = '1,2,4,5,6'
--这里漏了一个空格
set @pat = '1,2,4,5,6'set @pat = replace(@pat,',','%')
set @pat = '%'+@pat+'%'if patindex(@pat, '1,2,3,4,5,6')>0
print '查到'
else
print '查不到'
declare @pat varchar(100)
--set@pat = '1,2,4,5,6'
--这里漏了一个空格
set @pat = '1,2,4,5,6'set @pat = replace(@pat,',','%')
set @pat = '%'+@pat+'%'if patindex(@pat, '1,2,3,4,5,6')>0
print '查到'
else
print '查不到'
==============================================================
谢谢!我试试~
@WorkTime varchar(20),@NowJobtype varchar(50),
@NowJobName varchar(50),@ISManage char(1),
@Area varchar(30),@Trade varchar (50),@RefurbishTime DateTime,
@Language varchar(20),@LS varchar(10),@StartTime DateTime,@EndTime DateTime,@Company varchar(50)
AS
select UB_UserName,UB_Tname,UB_Brithday,UB_Habitation,UB_SEX,UB_Eduction,UB_WorkTime,UJ_NowJobtype,UEI_Company,UJ_Nowtrade,UJ_NowJobName,UJ_ISManage,UJ_Area,UJ_Trade,UR_RefurbishTime,ULI_Language,ULI_LS from (select a.*,b.*,c.*,d.*,e.* from dbo.U_Base_Info as a, dbo.U_Job_Intent as b,dbo.U_Resume_Manage as c,dbo.U_Lang_Info as d,dbo.U_Experience_Info as e where a.UB_UserName = b.UJ_UserName and b.UJ_UserName = c.UR_BelongUser and c.UR_BelongUser = d.ULI_UserName and d.ULI_UserName = e.UEI_UserName and e.UEI_Btime in (select max(UEI_Btime) from dbo.U_Experience_Info group by dbo.U_Experience_Info.UEI_UserName ) ) as Exp1
where ( UB_Habitation like '%'+@Habitation+'%' or @Habitation = '' ) and
( UB_SEX like '%'+@Sex+'%' or @Sex = '' ) and
( UB_Eduction like '%'+@Eduction+'%' or @Eduction = '' ) and
( UB_WorkTime like '%'+@WorkTime+'%' or @WorkTime = '' ) and
( UJ_NowJobtype like '%'+@NowJobtype+'%' or @NowJobtype = '' ) and
( UJ_NowJobName like '%'+@NowJobName+'%' or @NowJobName = '' ) and
( UJ_ISManage like '%'+@ISManage+'%' or @ISManage = '' ) and
( (set @Language = replace(@Language,',','%')
set @Language = '%' + @Language + '%')
patindex (@Language,ULI_Language)) or ULI_Language = ''
) and
( UJ_Area like '%'+@Area+'%' or @Area = '' ) and
( UJ_Trade like '%'+@Trade+'%' or @Trade = '' ) and
( UR_RefurbishTime <= @RefurbishTime or @RefurbishTime = null ) and
( ULI_LS like '%'+@LS+'%' or @LS = '' ) and
( UJ_Trade like '%'+@Trade+'%' or @Trade ='' ) and
( (UB_Brithday >= @StartTime and UB_Brithday <= @EndTime) or (@StartTime = null and @EndTime = null) ) and
( UEI_Company like '%'+@Company+'%' or @Company = '' )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
服务器: 消息 156,级别 15,状态 1,过程 MoreConditionSlect,行 16
在关键字 'set' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 MoreConditionSlect,行 17
第 17 行: ')' 附近有语法错误。
--在 where 里能“set”吗?
*/
ALTER Proc MoreConditionSlect
@Habitation varchar(100),@Sex char(1),@Eduction varchar(10),
@WorkTime varchar(20),@NowJobtype varchar(50),
@NowJobName varchar(50),@ISManage char(1),
@Area varchar(30),@Trade varchar (50),@RefurbishTime DateTime,
@Language varchar(20),@LS varchar(10),@StartTime DateTime,@EndTime DateTime,@Company varchar(50)
ASset @Language = replace(@Language,',','%')
set @Language = '%' + @Language + '%'select UB_UserName,UB_Tname,UB_Brithday,UB_Habitation,UB_SEX,UB_Eduction,UB_WorkTime,UJ_NowJobtype,UEI_Company,UJ_Nowtrade,UJ_NowJobName,UJ_ISManage,UJ_Area,UJ_Trade,UR_RefurbishTime,ULI_Language,ULI_LS from (select a.*,b.*,c.*,d.*,e.* from dbo.U_Base_Info as a, dbo.U_Job_Intent as b,dbo.U_Resume_Manage as c,dbo.U_Lang_Info as d,dbo.U_Experience_Info as e where a.UB_UserName = b.UJ_UserName and b.UJ_UserName = c.UR_BelongUser and c.UR_BelongUser = d.ULI_UserName and d.ULI_UserName = e.UEI_UserName and e.UEI_Btime in (select max(UEI_Btime) from dbo.U_Experience_Info group by dbo.U_Experience_Info.UEI_UserName ) ) as Exp1
where ( UB_Habitation like '%'+@Habitation+'%' or @Habitation = '' ) and
( UB_SEX like '%'+@Sex+'%' or @Sex = '' ) and
( UB_Eduction like '%'+@Eduction+'%' or @Eduction = '' ) and
( UB_WorkTime like '%'+@WorkTime+'%' or @WorkTime = '' ) and
( UJ_NowJobtype like '%'+@NowJobtype+'%' or @NowJobtype = '' ) and
( UJ_NowJobName like '%'+@NowJobName+'%' or @NowJobName = '' ) and
( UJ_ISManage like '%'+@ISManage+'%' or @ISManage = '' ) and
( patindex(@Language,ULI_Language)>0 or ULI_Language = '') and
( UJ_Area like '%'+@Area+'%' or @Area = '' ) and
( UJ_Trade like '%'+@Trade+'%' or @Trade = '' ) and
( UR_RefurbishTime <= @RefurbishTime or @RefurbishTime = null ) and
( ULI_LS like '%'+@LS+'%' or @LS = '' ) and
( UJ_Trade like '%'+@Trade+'%' or @Trade ='' ) and
( (UB_Brithday >= @StartTime and UB_Brithday <= @EndTime) or (@StartTime = null and @EndTime = null) ) and
( UEI_Company like '%'+@Company+'%' or @Company = '' )
GO
能实现下面的功能吗?declare @pat varchar(100)
set @pat = '1,2,4,5,6'set @pat = replace(@pat,',','%')
set @pat = '%'+@pat+'%'if patindex(@pat, '1,2,3,4,5,6')>0
print '查到'
else
print '查不到'谢谢~
---这样?????
--------------------------------------------------------------------------
Create Proc MoreConditionSlect @Habitation varchar(100),@Sex char(1),@Eduction varchar(10),
@WorkTime varchar(20),@NowJobtype varchar(50),
@NowJobName varchar(50),@ISManage char(1),
@Area varchar(30),@Trade varchar (50),@RefurbishTime DateTime,
@Language varchar(20),@LS varchar(10),@StartTime DateTime,@EndTime DateTime,@Company varchar(50)
AS
select UB_UserName,UB_Tname,UB_Brithday,UB_Habitation,UB_SEX,UB_Eduction,UB_WorkTime,UJ_NowJobtype,UEI_Company,UJ_Nowtrade,UJ_NowJobName,UJ_ISManage,UJ_Area,UJ_Trade,UR_RefurbishTime,ULI_Language,ULI_LS from (select a.*,b.*,c.*,d.*,e.* from dbo.U_Base_Info as a, dbo.U_Job_Intent as b,dbo.U_Resume_Manage as c,dbo.U_Lang_Info as d,dbo.U_Experience_Info as e where a.UB_UserName = b.UJ_UserName and b.UJ_UserName = c.UR_BelongUser and c.UR_BelongUser = d.ULI_UserName and d.ULI_UserName = e.UEI_UserName and e.UEI_Btime in (select max(UEI_Btime) from dbo.U_Experience_Info group by dbo.U_Experience_Info.UEI_UserName ) ) as Exp1
where ( UB_Habitation like '%'+@Habitation+'%' or @Habitation = '' ) and
( UB_SEX like '%'+@Sex+'%' or @Sex = '' ) and
( UB_Eduction like '%'+@Eduction+'%' or @Eduction = '' ) and
( UB_WorkTime like '%'+@WorkTime+'%' or @WorkTime = '' ) and
( UJ_NowJobtype like '%'+@NowJobtype+'%' or @NowJobtype = '' ) and
( UJ_NowJobName like '%'+@NowJobName+'%' or @NowJobName = '' ) and
( UJ_ISManage like '%'+@ISManage+'%' or @ISManage = '' ) and
(
(PatIndex('%'+Replace(@Language,',','%')+'%',ULI_Language)>0) or ULI_Language = ''
) and
( UJ_Area like '%'+@Area+'%' or @Area = '' ) and
( UJ_Trade like '%'+@Trade+'%' or @Trade = '' ) and
( UR_RefurbishTime <= @RefurbishTime or @RefurbishTime = null ) and
( ULI_LS like '%'+@LS+'%' or @LS = '' ) and
( (UB_Brithday >= @StartTime and UB_Brithday <= @EndTime) or (@StartTime = null and @EndTime = null) ) and
( UEI_Company like '%'+@Company+'%' or @Company = '' )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
======================================================================
declare @Language varchar(100)
set @Language = '1132,1142,1131'if PatIndex('%'+Replace(@Language,',','%')+'%','1131,1132,1133,1142,1146')>0
print '查到'
else
print '查不到'===========================================================
这样查不出来,在这种情形下我需要她查出结果~
谢谢~~~
======================================================================
declare @Language varchar(100)
set @Language = '1132,1142,1131'if PatIndex('%'+Replace(@Language,',','%')+'%','1131,1132,1133,1142,1146')>0
print '查到'
else
print '查不到'===========================================================
这样查不出来,在这种情形下我需要她查出结果~
谢谢~~~
---------------------------------------
我一开始就问你:按顺序包含么?
用"2,1,4,5,6"查能不能得到"1,2,3,4,5,6"?你不答,只能写按顺序包含的。
对不起,我错了,给你添麻烦了~
不按顺序也要找出来,要写个函数,就不是这么简单了。
=======================================
在存储过程里面写函数?
我是个菜鸟,麻烦高手指点一下~
如果@find子串在@field中都出现,返回1,否则返回0。
*/
create function fn_exists (@find varchar(1000), @Field varchar(1000))
returns bit
as
begin
declare @return bit
set @return=1
--去左右空格
set @find=ltrim(rtrim(@find))+','
while charindex(',',@find)>0
begin
if charindex(left(@find,charindex(',',@find)-1),@field)=0
begin
set @return=0
break
end
set @find = right(@find,len(@find)-charindex(',',@find))
end
return(@return)
end
go
如果@find子串在@field中都出现,返回1,否则返回0。
*/
create function fn_exists (@find varchar(1000), @Field varchar(1000))
returns bit
as
begin
declare @return bit
set @return=1
--去左右空格
set @find=ltrim(rtrim(@find))+','
while charindex(',',@find)>0
begin
if charindex(left(@find,charindex(',',@find)-1),@field)=0
begin
set @return=0
break
end
set @find = right(@find,len(@find)-charindex(',',@find))
end
return(@return)
end
go
=======================================
太谢谢了~
这段能放在存储过程中吗?怎么调用他?
@WorkTime varchar(20),@NowJobtype varchar(50),
@NowJobName varchar(50),@ISManage char(1),
@Area varchar(30),@Trade varchar (50),@RefurbishTime DateTime,
@Language varchar(20),@LS varchar(10),@StartTime DateTime,@EndTime DateTime,@Company varchar(50)
AS
select UB_UserName,UB_Tname,UB_Brithday,UB_Habitation,UB_SEX,UB_Eduction,UB_WorkTime,UJ_NowJobtype,UEI_Company,UJ_Nowtrade,UJ_NowJobName,UJ_ISManage,UJ_Area,UJ_Trade,UR_RefurbishTime,ULI_Language,ULI_LS from (select a.*,b.*,c.*,d.*,e.* from dbo.U_Base_Info as a, dbo.U_Job_Intent as b,dbo.U_Resume_Manage as c,dbo.U_Lang_Info as d,dbo.U_Experience_Info as e where a.UB_UserName = b.UJ_UserName and b.UJ_UserName = c.UR_BelongUser and c.UR_BelongUser = d.ULI_UserName and d.ULI_UserName = e.UEI_UserName and e.UEI_Btime in (select max(UEI_Btime) from dbo.U_Experience_Info group by dbo.U_Experience_Info.UEI_UserName ) ) as Exp1
where ( UB_Habitation like '%'+@Habitation+'%' or @Habitation = '' ) and
( UB_SEX like '%'+@Sex+'%' or @Sex = '' ) and
( UB_Eduction like '%'+@Eduction+'%' or @Eduction = '' ) and
( UB_WorkTime like '%'+@WorkTime+'%' or @WorkTime = '' ) and
( UJ_NowJobtype like '%'+@NowJobtype+'%' or @NowJobtype = '' ) and
( UJ_NowJobName like '%'+@NowJobName+'%' or @NowJobName = '' ) and
( UJ_ISManage like '%'+@ISManage+'%' or @ISManage = '' ) and
(
(dbo.fn_exists(@Language,ULI_Language)=1) or ULI_Language = ''
) and
( UJ_Area like '%'+@Area+'%' or @Area = '' ) and
( UJ_Trade like '%'+@Trade+'%' or @Trade = '' ) and
( UR_RefurbishTime <= @RefurbishTime or @RefurbishTime = null ) and
( ULI_LS like '%'+@LS+'%' or @LS = '' ) and
( (UB_Brithday >= @StartTime and UB_Brithday <= @EndTime) or (@StartTime = null and @EndTime = null) ) and
( UEI_Company like '%'+@Company+'%' or @Company = '' )
GO
创建函数:fn_exists。在存储过程中调用函数判断。
======================================
明白,谢谢@_@
同时也感谢各位朋友的支持~
路过讨论一下,
declare @pat varchar(100)
set @pat = '1'
set @pat = replace(@pat,',','%')
print @pat
set @pat = '%'+@pat+'%'
print @pat
if patindex(@pat, '11,2,3,4,5,6')>0
print '查到'
else
print '查不到'
这种按道理也是查不到,但是现在是查到,请高手解决,这个问题?
如果只能是1到9吗?