Help: About ms sql query, how can i check if a part string exists in a string?

Hello to all,

I have a problem with ms sql query. I hope that somebody can help me.

i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .

3387 (2345, 2388,4567,...)

4567 (8990, 7865, 3387...)

i wirte a query to check if there is Relationship between two members.

Query:

Declare @.IDM int; Declare @.IDO int; Set @.IDM = 3387, @.IDO = 4567;

select *

from Relationship

where(IDMember= @.IDM)and( cast(@.ID0 as char(100)) in

(select Relationship.[RelationshipIDs]from Relationshipwhere IDMember= @.IDM))

But I get nothing by this query.

Can Someone tell me where is the problem? Thanks

Best Regards

Pinsha

Use the PATINDEX function to check if a one part is there in a string or not and you can write your query like this

Select * from RelationShip

Where (IDMember=@.IDM)

AND ( PATINDEX(@.IDO, RelationshipIDs) > 0 ) -- If the string is found this will return greater than 0 the starting of the string

This should help you

|||

try this, you have to tweak it a little to work correctly with small and big numbers ( like it will return 312 as connected to 12 but you can try to fix it your self for example by including separators between related ids)

createTable #Relationships

(IDMemberint,

Relationshipsvarchar(1000))

insertinto #Relationships

select' 3387','(2345, 2388,4567,...)'

insertinto #Relationships

select'4567','(8990, 7865, 3387...)'

Declare @.IDMint,

@.IDOint

Set @.IDM= 3387

SET @.IDO= 4567;

select*

from #Relationships

where(IDMember= @.IDM)and(charindex(cast(@.IDOasvarchar(100)),cast(Relationshipsasvarchar(1000)))>0)

droptable #Relationships

|||

Hello satya_tanwar,

i have tried to use the PATINDEX function, but if i used a variable in PATINDEX(@.IDO, RelationshipIDs) and (@.IDO = '3456', i doesn't work. If i use a constant in PATINDEX('3456', RelationshipIDs), it works.

I use ms sql 2005 to execute this query. Maybe can you tell me where is the problem?

Thank you very much

Best Regards

Pinsha

|||

Hallo japzgier,

I have also tried to use your example. But the variable incharindex function doesn't work. If i use constant incharindex function and it works.

i don't know whycharindex doesn't work with variable.

Maybe can you help me?

Thank you very much

Best Regards

Pinsha

|||

What is the variable type Pinsha...Could you please send the code to me coz i have tried to execute the query and its working. Whats result you are getting ?

or just try to modify the PatIndex part like this

(PATINDEX('%'+ @.IDO+'%', Relationships)> 0)

Will work for sure..

|||

I tested code I passed on my SQL server and I have no problems with it, I use SQL 2005 which version are you using?

|||

Hello to japzgier,

I found the straight problem. The problem is false data type. i habe defined a variable as char type and another variable as varchar type.

i use ms sql 2005.

Thanks very much

But i have now another problem, Maybe could you help me?

In my tablewtcomValidRelationships

IDMember RelationshipIDS

1 2

2 3

3 4

4 5

5 6

declare @.ENDPATH varchar(1000);

declare @.IDMint;

declare @.IDO varchar(100);

set @.IDM= 1;

set @.IDO='6'

declare @.Path3 varchar(1000);

set @.Path3=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)

from wtcomValidRelationshipsas A,

wtcomValidRelationshipsas B,

wtcomValidRelationshipsas C,

wtcomValidRelationshipsas D,

wtcomValidRelationshipsas E

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0

andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0

andcharindex(@.IDO,E.RelationshipIDs)> 0);

if(len(@.Path3)> 0)

begin

set @.ENDPATH= @.Path3;

end

else

begin

set @.ENDPATH='No Relationship';

end

print @.ENDPATH

Problem is that Query Excuting need to 38 Secends ?

Thanks

Best Regards

Pinsha

|||

Hi sasha,

Have you tried this

(PATINDEX('%'+ @.IDO+'%', Relationships)> 0)

|||

Hello satya,

i have tried this (PATINDEX('%'+ @.IDO+'%', Relationships)> 0) , it works. Thanks

but now i have another problem.

I need to search the relationships from level 1 to level 6.

so i need write aIF ELSE sql query. But when i useIF ELSE, the query excuting is too slow. without if else, the query excuting is rash.

But i must useIF ELSE to get my result. Maybe Could you tell me other methods, how can i check the first level query, if it has no elements and then go to excute next level , and continue do this compare. My complete Codes are here. I hope that you can understand what i mean. Because my english is poor . sorry

For Example

wtcomValidRelationships

IDMember RelationshipIDs

1 2

2 3

3 4

4 5

5 6

Without IF ELSE 5 select Query together excuted under 1 Seconds:

declare @.ENDPATH varchar(1000);

declare @.IDMint;

declare @.IDO varchar(100);

set @.IDM= 1;

set @.IDO='6'

select IDMember

from wtcomValidRelationships

where wtcomValidRelationships.[IDMember]= @.IDM

andPATINDEX('%'+@.IDO+'%',(select RelationshipIDsfrom wtcomValidRelationshipswhere IDMember= @.IDM))> 0

selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andPATINDEX('%'+@.IDO+'%',B.RelationshipIDs)> 0

selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andPATINDEX('%'+@.IDO+'%',C.RelationshipIDs)> 0

selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andPATINDEX('%'+@.IDO+'%',D.RelationshipIDs)> 0

selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D, wtcomValidRelationshipsas E

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0andPATINDEX('%'+@.IDO+'%',E.RelationshipIDs)> 0

But With IF ELSE query excuted more than 30 seconds

declare @.ENDPATH varchar(1000);

declare @.IDMint;

declare @.IDO varchar(100);

declare @.Level int;

set @.IDM= 3450;

set @.IDO='4269'

declare @.Path varchar(1000);

set @.Path='';

set @.Path=(select IDMember

from wtcomValidRelationships

where wtcomValidRelationships.[IDMember]= @.IDM

andcharindex( @.IDO,(select RelationshipIDsfrom wtcomValidRelationshipswhere IDMember= @.IDM))> 0);

if(len(@.Path)> 0)

begin

set @.ENDPATH= @.Path;

end

else

begin

declare @.Path1 varchar(1000);

set @.Path1=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

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

if(len(@.Path1)> 0)

begin

set @.ENDPATH= @.Path1;

end

else

begin

declare @.Path5 varchar(1000);

set @.Path5=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(@.IDO,C.RelationshipIDs)> 0);

if(len(@.Path5)> 0)

begin

set @.ENDPATH= @.Path5;

end

else

begin

declare @.Path2 varchar(1000);

set @.Path2=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(@.IDO,D.RelationshipIDs)> 0);

if(len(@.Path2)> 0)

begin

set @.ENDPATH= @.Path2;

end

else

begin

declare @.Path3 varchar(1000);

set @.Path3=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D, wtcomValidRelationshipsas E

where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0andcharindex(@.IDO,E.RelationshipIDs)> 0);

if(len(@.Path3)> 0)

begin

set @.ENDPATH= @.Path3;

end

else

begin

set @.ENDPATH='No Relationship'

end

end

end

end

end

print @.ENDPATH