how to find more occurance

Posted by Santosh4u under Sql Server on 10/11/2013 | Points: 10 | Views : 1196 | Status : [Member] | Replies : 2
Hi,
how do i find the more occurrence words from a content in sql server.

Ex: i have a table called CV_Upload having Columns Id,CV_Content,Primary_Skill and i am storing CV Content into the CV_Content column of CV_Upload table and now i want to find the primary skill from this content and update in primary_Skill column.
means the technical skill(ex: .net, java, testing) which repeated more times in content and this All technical skills i am keeping in a different table.

Please let me know if needs more clarification.

Thanks
Santosh




Responses

Posted by: Bandi on: 11/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--Sample data of tables: @CV_Upload , @CV_Content  and @Skills 
DECLARE @CV_Upload TABLE( Id int,CV_Content varchar(max),Primary_Skill varchar(100))
insert into @CV_Upload values(1, 'asdfsad sdiaksaj asoeiw o waejwqoe wo 17 You are designing a new query that will eventually be used by a new software application. It is important that the application testing knows the id value testing of the last row that was inserted. Rather than re-run another query just to get the maximum id value, your friend has told java you to include an inbuilt function. What might that function be 18) Your manager has asked you to check the index stats for a particular table called Address. You have executed the following query (see below) and it reported that the avg_fragmentation_in_percent is 15%. What should you do?', NULL),
(2,
'You are designing a new table that will hold information about medical records. Another table java called documents contains a foreign key that references this table. To make sure information is cleaned up java when a delete operation is testing performed, you have added an ON DELETE CASCADE statement to the foreign key relationship. What will happen when a row is deleted from the medical records table?' , NULL
)


declare @CV_Content TABLE ( id int, content varchaR(max) )
insert into @CV_Content values(1, 'asdfsad sdiaksaj asoeiw o waejwqoe wo 17 You are designing a new query that will eventually be used by a new software application. It is important that the application testing knows the id value testing of the last row that was inserted. Rather than re-run another query just to get the maximum id value, your friend has told java you to include an inbuilt function. What might that function be 18) Your manager has asked you to check the index stats for a particular table called Address. You have executed the following query (see below) and it reported that the avg_fragmentation_in_percent is 15%. What should you do?'),
(2,
'You are designing a new table that will hold information about medical records. Another table java called documents contains a foreign key that references this table. To make sure information is cleaned up java when a delete operation is testing performed, you have added an ON DELETE CASCADE statement to the foreign key relationship. What will happen when a row is deleted from the medical records table?'
)

declare @Skills table( words varchar(50))
insert @Skills values('net'), ('java'), ('testing')


--UPDATE statement
UPDATE @CV_UPLOAD
SET Primary_Skill = cvContent.words
FROM @CV_UPLOAD cvUpload
JOIN (SELECT id, SUM((LEN(content) - LEN(REPLACE(content, words, '')))/LEN(words)) NumOfTimes, words,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY SUM((LEN(content) - LEN(REPLACE(content, words, '')))/LEN(words)) DESC) RN
FROM @CV_Content
CROSS APPLY @Skills
GROUP BY id, words
) cvContent ON CVcontent.id = cvUpload.id
WHERE RN=1

--Check data after UPDATE
SELECT * FROM @CV_UPLOAD


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Table: CV_UPLOAD  
columns are Id ,CV_Content ,Primary_Skill

Table: CV_Content
columns are id , content

Table: Skills
columns are words

Query should be as follows:

UPDATE cvUpload
SET cvUpload.Primary_Skill = cvContent.words
FROM CV_UPLOAD cvUpload
JOIN
(SELECT id
, SUM((LEN(content) - LEN(REPLACE(content, words, '')))/LEN(words)) NumOfTimes
, words
, ROW_NUMBER() OVER (PARTITION BY id ORDER BY SUM((LEN(content) - LEN(REPLACE(content, words, '')))/LEN(words)) DESC) RN
FROM CV_Content
CROSS APPLY Skills
GROUP BY id, words
) cvContent ON CVcontent.id = cvUpload.id
WHERE RN=1


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response