Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

safzal1212
Starting Member

11 Posts

Posted - 2010-01-07 : 14:41:32

I have a table tblContent in which I have the follwoing fields

ContentID(int)
DocumetNo(int)
RevisionNo(decimal)
Content(TEXT)
StatusID(int)

I need to write a query that fetch all the document nos where "statusid=5" and then need to check that if any of those document numbers are refered inside the content field. If yes then feth the record. I don't want to do it using temp tables so if somebody could point me to right direction then I shall be really thankfull.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-07 : 14:57:04

Select * from tblContent where statusid = 5 and content like '%' + DocumentNo + '%'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 14:59:19

Some sample data and expected output would help...but maybe this..

select a.*
from tblContent inner join 
(
select DocumetNo from tblContent where StatusID = 5
)b
on a.Content like '%' + b.DocumetNo + '%'

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-07 : 15:00:44

Vijay, I was able to produce results without any join at all...

Declare @myTble table (fruit varchar(10), ID int , Name varchar(20))

Insert @myTble
Select 'Apple',1, 'My Apple' Union
Select 'Grape', 2, 'No' Union
Select 'Orange', 2, 'Orange u Glad' Union
Select 'Banana' , 1, 'Wanna Banana'

Select * from @myTble a where ID = 1 and Name Like '%' + fruit + '%'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 15:13:01

That is true...when the OP said

quote:
and then need to check that if any of those document numbers are refered inside the content field

I decided a JOIN is needed..

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-07 : 15:23:29

lol not a problem, both produce the same results tho correct? He can't go wrong! (unless there is some conversion problem changing documentNo to text, but I thought that may be done implicitly. :)

safzal1212
Starting Member

11 Posts

Posted - 2010-01-07 : 15:49:55

Thanks you guys for the help. I have changed the query a bit as it was giving some errors and just put an alias "a" after tblcontents. But now I am getting one different error "Argument data type int is invalid for argument 2 of like function." As I said I can do it using temp table but if I get some better solution then I will prefer that.

select a.*
from tblContents a inner join
(
select DocumentNo from tblContents where StatusID = 5
)b
on a.Content like '%' + b.DocumentNo + '%'

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-07 : 15:56:50

You probably have to convert the DocumentNo, since its an Int. try:

a.Content like '%' + cast(b.DocumentNo as Varchar(20)) + '%'

NB: Change the 20 to however long your field can be.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 15:58:54

Thats exactly what DP978 has mentioned in his previous post..
use..
a.Content like '%' + convert(varchar(20),b.DocumetNo) + '%'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 15:59:55

by DP978

kinda funny that we both used varchar(20)

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-07 : 16:03:56

Haha agreed :)

safzal1212
Starting Member

11 Posts

Posted - 2010-01-07 : 16:18:17

Thats brillient guys. Thanks for your help.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 16:57:40

Np. You're welcome.

safzal1212
Starting Member

11 Posts

Posted - 2010-01-11 : 10:30:04

Guys,
I am having one issue with this query. Its also returning the partial results. For expample if I search for DocID=100 it also returns the document with ID=1000. Any comments?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 10:35:19

Can you show some sample data from the table..we can see if we can find a pattern.

safzal1212
Starting Member

11 Posts

Posted - 2010-01-11 : 10:53:01

I think the problem is somewhere in the like operation that we have for this query a.Content like
'%' + convert(varchar(20),b.DocumetNo) + '%'

I am posting some sample data here

ContentID DocumetNo RevisionNo Content statusid
1 1000 0.0 Test document href=content.aspx? docid=1590 6
2 1000 0.1 Test document href=content.aspx?docid=1590 4
3 1590 0.0 Doc is to elaborate the test process 5
4 1600 0.0 Another Test Document href=content.aspx?docid=159 4
5 159 0.0 Another Doc to elaborate test process 4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 10:57:41

Do you mean this?


SELECT t1.*
FROM Table t1
JOIN (SELECT DocumetNo FROM Table WHERE StatusId=5) t2
ON PATINDEX('%'+ t2.DocumentNo +'%',CONVERT(varchar(max),Content))>0

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 10:59:51

If the 'Content' field always ends with the documentno if there is a link in it...you could try using...

Select * from tblContent where statusid = 5 and content like '%' + convert(varchar(20),DocumentNo)

Remove the '%' from the end...Try it and let us know

safzal1212
Starting Member

11 Posts

Posted - 2010-01-11 : 11:16:25

@visakh16: its giving me the follwoing error
'Conversion failed when converting the varchar value '%' to data type int."

@vijayisonly: No it doesn't end with the document no all the time. That link is most of the time is between the content data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 11:17:54

quote:
Originally posted by safzal1212

@visakh16: its giving me the follwoing error
'Conversion failed when converting the varchar value '%' to data type int."

@vijayisonly: No it doesn't end with the document no all the time. That link is most of the time is between the content data.

ok so Document No was int?


SELECT t1.*
FROM Table t1
JOIN (SELECT DocumetNo FROM Table WHERE StatusId=5) t2
ON PATINDEX('%'+ CAST(t2.DocumentNo AS varchar(15)) +'%',CONVERT(varchar(max),Content))>0

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 11:23:49

quote:
Originally posted by visakh16

quote:
Originally posted by safzal1212

@visakh16: its giving me the follwoing error
'Conversion failed when converting the varchar value '%' to data type int."

@vijayisonly: No it doesn't end with the document no all the time. That link is most of the time is between the content data.

ok so Document No was int?

SELECT t1.*
FROM Table t1
JOIN (SELECT DocumetNo FROM Table WHERE StatusId=5) t2
ON PATINDEX('%'+ CAST(t2.DocumentNo AS varchar(15)) +'%',CONVERT(varchar(max),Content))>0

Visakh, This will also give same result...No?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources