Improve company productivity with a Business Account.Sign Up

x
?
Solved

update rank of tag if word in mentioned in the caption

Posted on 2014-01-29
6
Medium Priority
?
299 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 1800 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 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 200 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Closing Comment

by:ethar1
ID: 39818070
thanks guys
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

606 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