like使用

[lgjlry]

表中的两个字段

i_merchandiseid s_includevaid
--------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 字段的内容
1661 523,
5891 544,55,6,32,54,
10155 544,33,55,32,54,
14896 544,33,6,32,54,
19343 544,33,55,6,32,54,
23399 6756,233,55,66,7,3,5,43,
73771 6756,233,55,66,3,2,4,5,43,
77914 6756,233,55,66,7,3,2,4,5,43,
81930 6756,233,55,66,7,3,2,4,43,
-----根据字段的内容创建表p_sobigo_tmerchandiseparavalueid




create proc dddt
(@gg varchar(64))
as
declare @ff varchar(64)
set @ff=replace(@gg,',',',%')
print @ff
select i_merchandiseid,s_includevaid from p_sobigo_tmerchandiseparavalueid where s_includevaid like @ff
-------------------------------------------

有什么语句代替 like 语句来实现模糊查找？？？？？？

[zlp321002]

--charindex 可以啊
select
i_merchandiseid,
s_includevaid
from
p_sobigo_tmerchandiseparavalueid
where
charindex(','+s_includevaid,','+'22'+,')>0

[vivianfdlpw]

create function f_tb(@str varchar(8000))
returns @tb table(s varchar(20))
as
begin
while charindex(',',@str)>0
begin
insert @tb select left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
end

insert @tb select @str
return
end
go


create proc dddt
(
@gg varchar(64)
)
as
select i_merchandiseid
,s_includevaid
from p_sobigo_tmerchandiseparavalueid t
where exists(select 1
from f_tb(@gg)
where charindex(','+s+',',','+t.+s_includevaid',')>0)

go

[lisiyong]

-------------测试数据

create table person
(
sid int primary key identity(1000,1),
age int,
sex varchar(10),
ismarriged varchar(10)
)
--ismarried婚否
insert into person select 20,'male','true'
union select 31,'female','true'
union select 28,'male','false'
union select 23,'male','true'
union select 34,'female','false'
union select 25,'female','false'
union select 35,'female','true'
union select 24,'male','true'
union select 29,'female','true'
union select 30,'male','false'
union select 31,'female','true'
union select 21,'female','true'
union select 22,'male','false'
union select 32,'female','true'
union select 27,'male','true'
union select 27,'female','true'
union select 33,'female','false'
union select 22,'male','false'
union select 24,'male','false'
union select 25,'male','true'
union select 34,'female','false'

--------------------查询age中有0的记录:
select *
from person
where charindex('0',age)>0

-------------------结果:


1000 20 male true
1012 30 male false

[vivianfdlpw]

多了个逗号

create function f_tb(@str varchar(8000))
returns @tb table(s varchar(20))
as
begin
while charindex(',',@str)>0
begin
insert @tb select left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
end

insert @tb select @str
return
end
go


create proc dddt
(
@gg varchar(64)
)
as
select i_merchandiseid
,s_includevaid
from p_sobigo_tmerchandiseparavalueid t
where exists(select 1
from f_tb(@gg)
where charindex(','+s+',',','+t.s_includevaid)>0)

go

[lisiyong]

select *
from person
where PATINDEX('%f%',sex)>0

-------------------------------------结果:

1001 21 female true
1006 25 female false
1008 27 female true
1011 29 female true
1013 31 female true
1014 32 female true
1015 33 female false
1016 34 female false
1017 35 female true


不过有点注意:

PATINDEX 函数只能对 char、nchar、varchar、nvarchar、text 和 ntext 数据类型进行操作。
可以使用通配符

[vivianfdlpw]

create table A
(
i_merchandiseid int,
s_includevaid varchar(100)
)
insert A
select 1661, '523,' union
select 5891, '544,55,6,32,54,' union
select 10155, '544,33,55,32,54,'
go

create function f_tb(@str varchar(8000))
returns @tb table(s varchar(20))
as
begin
while charindex(',',@str)>0
begin
insert @tb select left(@str,charindex(',',@str)-1)
set @str=stuff(@str,1,charindex(',',@str),'')
end

insert @tb select @str
return
end
go


create proc dddt
(
@gg varchar(64)
)
as
select i_merchandiseid
,s_includevaid
from A t
where exists(select 1
from f_tb(@gg)
where charindex(','+s+',',','+t.s_includevaid)>0)

go

--调用
exec dddt '55,523'
exec dddt '33,523'

--删除测试环境
drop function f_tb
drop table A


--结果
/*
i_merchandiseid s_includevaid
--------------- ---------------
1661 523,
5891 544,55,6,32,54,
10155 544,33,55,32,54,

（所影响的行数为 3 行）

i_merchandiseid s_includevaid
--------------- -----------------
1661 523,
10155 544,33,55,32,54,

（所影响的行数为 2 行）
*/

[lgjlry]

6756,233,55,66,7,3,2,4,5,43,
544,33,55,6,32,54,
查询33 的时候不要把含有　233的记录也检索出来

[lgjlry]

vivianfdlpw 你的查询速度我测试好像没有like 的速度快

[zoubsky]

关注～～～

[lgjlry]

exec dddt '55,523'
exec dddt '33,523'
-----------------------------
i_merchandiseid s_includevaid
--------------- ---------------
1661 523,
5891 544,55,6,32,54,
10155 544,33,55,32,54, ------查询的是含有55 与523 的数据，怎么只有523的记录也检索到了
-------------------------
条件是即满足33，又满足523，得
例如：有这样的记录： 5891 544,33,55,6,32,54,

5866 544，55，
5877 33，6，
4341 544，6，
6666 55，3，544，
查询 544，55，
结果 5866
6666
是不会出现4341 这条记录的

[lgjlry]

顶