Solved

update rank of tag if word in mentioned in the caption

Posted on 2014-01-29
6
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EF5 How do I stop pre-compiled views? 8 54
Section based report in SSRS 14 33
Need split for SQL data 7 48
Importing from CSV to SSMS 2 7
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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