Help: about charindex function doesnt work with variable

Hello to all,

I hope that somebody can help me.

I have written a sql query to search Partner. I have a wtcomValidRelationships Table. There are two Fields(IDMember(type: Int) andRelationshipIDs(type: varchar(1000))in this table.

Example: 3418 has 3422 RelationshipID and 3422 has 4088 RelationshipID, if i want to check if there is a relationship between 3418 and 4088.

declare @.IDMint;

declare @.IDOchar(100);

set @.IDM= 3418;

set @.IDO='4088';

select B.IDMember

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMandcharindex(cast(B.IDMemberaschar(100)),A.RelationshipIDS)> 0

andcharindex(@.IDO,B.RelationshipIDs)> 0

Using this query i get nothing.

I try to use constant incharindex and i get result.

declare @.IDMint;

declare @.IDOchar(100);

set @.IDM= 3418;

set @.IDO='4088';

select B.IDMember

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMandcharindex('3422',A.RelationshipIDS)> 0

andcharindex('4088',B.RelationshipIDs)> 0

So i think that charindex doesn't work with variable. But I must use variable. Can someone help me? What should i do ?

Thanks

Best Regards

Pinsha

Hi Shasha,

See the earlier post and question you have asked about the same issue and use the PATINDEX i have updated the code. Just see the earlier question reply.