SELECT * FROM Product WHERE CategoryId LIKE '%"+cId+"%'" cId的值输成-55 不就可以了吗?
SELECT * FROM Product WHERE (CategoryId LIKE '%-'+ cId+ '-%') or (CategoryId LIKE '%-'+ cId)
SELECT * FROM Product WHERE CHARINDEX('-'+cId+'',CategoryId)>0 or CHARINDEX('-'+cId+'-',CategoryId)>0
SELECT * FROM Product WHERE CHARINDEX('-'+cId+'',right(CategoryId,len(cId)+1))>0 or CHARINDEX('-'+cId+'-',CategoryId)>0
SELECT * FROM Product WHERE CHARINDEX('-'+cId,right(CategoryId,len(cId)+1))>0 or CHARINDEX('-'+cId+'-',CategoryId)>0
不可以.这样的话 38-45-555 还是会被输出来declare @cId varchar(5) SELECT @cId='55' SELECT * FROM Product WHERE CategoryId LIKE '%-'+@cId+'-%' union SELECT * FROM Product WHERE CategoryId like '%-'+@cId
SELECT * FROM Product WHERE CategoryId LIKE '%_'+cId+'_%'
create table Product ( CategoryId varchar(30),) insert Product select '38-55-57' union select '38-5-55' union select '355-4-57' union select '38-45-57' union select '38-45-555' union select '38-45-155' union DECLARE @cid varchar(10) set @cid='55' SELECT * FROM Product WHERE CHARINDEX('-'+@cid,right(CategoryId,len(@cid)+1))>0 or CHARINDEX('-'+@cid+'-',CategoryId)>0
同意 libin_ftsafe(子陌红尘) qltouming(缘木渔人)的方法
以上方法都不好,应该是 SELECT * FROM Product WHERE CategoryId LIKE '%"+cId+"%'" 而CID的值是38-5
declare @cId char(8) set @cId = '55' select * from ( select '138-55-57' as CategoryId union select '238-5-55' as CategoryId union select '3355-4-57' as CategoryId union select '438-45-57' as CategoryId union select '538-455-555' as CategoryId union select '638-45-155' as CategoryId union select '755-45-155' as CategoryId union select '55-45-155' as CategoryId )Product where SUBSTRING(CategoryId,charindex(left(@cId,len(@cId)),CategoryId)-1,1) in ('-','') and SUBSTRING(CategoryId,charindex(left(@cId,len(@cId)),CategoryId)+len(@cId),1) in ('-','')
我希详细描述一下需求 如下语句SELECT * FROM Product WHERE CategoryId LIKE '%"+cId+"%'" 例如cid的值是55 这个值传过来的,无法变动的 CategoryId的值如下 38-55-57 38-5-55 355-4-57 38-45-57 38-45-555 38-45-155 也可能是33-5444-155-55-22-44或255-155-455-22-44,位数和长度不定 ..........我希望只列出包含55的,而忽略例如155,555 551等这类数字 例如 38-5-55 38-55-57 33-5444-155-55-22-44 而不需诸如38-45-155 255-155-455-22-44这类的
SELECT * FROM Product WHERE CategoryId LIKE '%-'+@cId+'-%' or CategoryId LIKE '%-'+@cId or CategoryId LIKE cId+'-%'
SELECT * FROM Product WHERE charindex(CategoryId,'-'+@cId)>0 or charindex(CategoryId,@cId+'-')>0
好像Contain的全文检索也可以.
SELECT * FROM Product WHERE CategoryId LIKE '%'+'-'+cId+'-'+'%' or categoryid like '%'+'-'+cId or CategoryId LIKE cId+'-'+'%'
select * from test where content like '%-55-%' or content like '%-55' or content like '55-%' or content like '55'
将楼猪需求做的分析:可能的情况为3种: 1、55在开头的,那么用substring(content,1,3)='55-' 2、55在中间的,那么用like '-55-' 3、55在结尾的,那么用substring(content,len(content)-3,3)='-55' SQL语句:select * from test where substring(content,1,3)='55-' or content like '-55-' or substring(content,len(content)-3,3)='-55'
哦哦哦哦,like忘记加%了~~~
declare @ss varchar(10) set @ss=55 SELECT * FROM Product WHERE CHARINDEX(@ss, CategoryId)>0 这样试一下
cId的值输成-55
不就可以了吗?
SELECT @cId='55'
SELECT * FROM Product WHERE CategoryId LIKE '%-'+@cId+'-%'
union
SELECT * FROM Product WHERE CategoryId like '%-'+@cId
( CategoryId varchar(30),)
insert Product
select '38-55-57' union
select '38-5-55' union
select '355-4-57' union
select '38-45-57' union
select '38-45-555' union
select '38-45-155' union
DECLARE @cid varchar(10)
set @cid='55'
SELECT * FROM Product WHERE CHARINDEX('-'+@cid,right(CategoryId,len(@cid)+1))>0 or CHARINDEX('-'+@cid+'-',CategoryId)>0
SELECT * FROM Product WHERE CategoryId LIKE '%"+cId+"%'"
而CID的值是38-5
set @cId = '55'
select * from
(
select '138-55-57' as CategoryId
union select '238-5-55' as CategoryId
union select '3355-4-57' as CategoryId
union select '438-45-57' as CategoryId
union select '538-455-555' as CategoryId
union select '638-45-155' as CategoryId
union select '755-45-155' as CategoryId
union select '55-45-155' as CategoryId
)Product
where
SUBSTRING(CategoryId,charindex(left(@cId,len(@cId)),CategoryId)-1,1) in ('-','')
and
SUBSTRING(CategoryId,charindex(left(@cId,len(@cId)),CategoryId)+len(@cId),1) in ('-','')
如下语句SELECT * FROM Product WHERE CategoryId LIKE '%"+cId+"%'"
例如cid的值是55 这个值传过来的,无法变动的
CategoryId的值如下
38-55-57
38-5-55
355-4-57
38-45-57
38-45-555
38-45-155
也可能是33-5444-155-55-22-44或255-155-455-22-44,位数和长度不定
..........我希望只列出包含55的,而忽略例如155,555 551等这类数字
例如
38-5-55 38-55-57 33-5444-155-55-22-44
而不需诸如38-45-155 255-155-455-22-44这类的
FROM Product
WHERE CategoryId LIKE '%-'+@cId+'-%'
or CategoryId LIKE '%-'+@cId
or CategoryId LIKE cId+'-%'
FROM Product
WHERE charindex(CategoryId,'-'+@cId)>0 or charindex(CategoryId,@cId+'-')>0
or categoryid like '%'+'-'+cId or CategoryId LIKE cId+'-'+'%'
1、55在开头的,那么用substring(content,1,3)='55-'
2、55在中间的,那么用like '-55-'
3、55在结尾的,那么用substring(content,len(content)-3,3)='-55'
SQL语句:select * from test where substring(content,1,3)='55-'
or content like '-55-'
or substring(content,len(content)-3,3)='-55'
set @ss=55
SELECT * FROM Product WHERE CHARINDEX(@ss, CategoryId)>0
这样试一下