Solved

update rank of tag if word in mentioned in the caption

Posted on 2014-01-29
6
256 Views
Last Modified: 2014-01-29
Dear all,
Need to update NewsSYSNewsTags.RankInCaption= 5
where NewsSYSTags.TagTitle IN STRING of News.NewsCaptionAR
and
News.NewsID=1011 and  NewsSYSNewsTags.NewsID=1011 and
NewsSYSNewsTags.NewsSYSTagsID  = NewsSYSTags.NewsSYSTagsID

thanks
0
Comment
Question by:ethar1
  • 4
6 Comments
 

Author Comment

by:ethar1
ID: 39817747
I have 3 tables
News : NewsID , NewsCaptionAR
NewsSYSTags : where I store unique tags (NewsSYSTagsID (pk) , TagTitle  )
NewsSYSNewsTags : where I store (NewsSYSNewsTagsID =pk) , NewsID , NewsSYSTagsID (fk)
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 450 total points
ID: 39817803
Try this:
update NewsSYSNewsTags
Set NewsSYSNewsTags.RankInCaption = 5 
From NewsSYSNewsTags, NewsSYSTags, News
where NewsSYSNewsTags.NewsSYSTagsID  = NewsSYSTags.NewsSYSTagsID
and News.NewsID = NewsSYSNewsTags.NewsID
and IndexOf(rtrim(NewsSYSTags.TagTitle), News.NewsCaptionAR) > 0 -- Tag appears in Caption
and News.NewsID = 1011 --Look for specific news

Open in new window

-Harish
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 50 total points
ID: 39817929
Harish, I think you meant CHARINDEX versus INDEXOF above.
CHARINDEX: http://technet.microsoft.com/en-us/library/ms186323.aspx

Note: you may get a few false positives if your tag is a common part of other words.  For example, suppose your tag is "bask" then it will match to "basket" and "basketball."  As another aside, if you enabled full-text search on SQL Server, you may be able to use those functions to assist you here.

Anyway, I just wanted to assist with the correct T-SQL function to find index of character/string as INDEXOF is not valid for Microsoft SQL Server.  You need CHARINDEX, PATINDEX, or full-text search like CONTAINS.

Full-text: http://technet.microsoft.com/en-us/library/ms142571.aspx

I hope that helps!
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Closing Comment

by:ethar1
ID: 39818070
thanks guys
0
 

Author Comment

by:ethar1
ID: 39818222
0
 

Author Comment

by:ethar1
ID: 39818496
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
Help creating a spatial object in SQL Server 4 23
Sql Server group by 10 29
SSRS Enable Remote Errors 4 26
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question