Solved

keywords

Posted on 2014-01-21
9
228 Views
Last Modified: 2014-01-29
Dear all,
Need to design a SP to collect all words in a nvarchar(max) field and add it to "Keywords" Table if not exist otherwise increase the count field +1.

Thanks,
sql 2008
0
Comment
Question by:ethar1
  • 7
9 Comments
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39799151
Can there be more than 1 keyword in the nvarchar(max) field for a single row of data? If so, how are the keywords separated?
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39800029
first you have to create a split function to get this done

You can know how this function works from here http://everysolution.wordpress.com/2011/07/28/udf-to-split-a-delimited-string-and-return-it-as-a-table/
I am copying the code for your reference

split function
drop function  dbo.Split
GO
CREATE FUNCTION dbo.Split
(
 @RowData nvarchar(MAX),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 DECLARE @index INT
 SET @index = Charindex(@SplitOn,@RowData)
 While (@index>0)
 Begin
 Insert Into @RtnValue (data)
  Select
 Data = ltrim(rtrim(Substring(@RowData,1,@index-1)))
 
 Set @RowData = Substring(@RowData,@index+1,len(@RowData))
 Set @Cnt = @Cnt + 1
 SET @index = Charindex(@SplitOn,@RowData)
 End
 
 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))
 
 Return
END

GO

Open in new window


Then you can apply the below logic for getting the things done

declare @KeyWords table
(
    word    VARCHAR(1000)
    ,Count  BIGINT
)

declare @input  nVARCHAR(max)
SET @input = 'A quick brown fox jumped over the hedge of your computer in a bussiness rather quick fashion'

select * FROM dbo.split(@input,' ')

;WITH C AS
(
    SELECT COUNT(1) CC,Data FROM dbo.split(@input,' ')
    GROUP BY DATA
)
UPDATE  K
SET     [count] = [count] + CC
FROM    @KeyWords K
JOIN    C
ON      K.word = C.data

;WITH C AS
(
    SELECT COUNT(1) CC,Data FROM dbo.split(@input,' ')
    GROUP BY DATA
)
INSERT INTO @KeyWords
SELECT DATA,CC FROM C
WHERE NOT EXISTS ( SELECT 1 FROM @KeyWords K WHERE K.word = C.data)

SELECT * FROM @KeyWords 

Open in new window

0
 

Author Comment

by:ethar1
ID: 39811242
ok, great , I will try it and come back to you
0
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

 

Author Closing Comment

by:ethar1
ID: 39817310
thanks
0
 

Author Comment

by:ethar1
ID: 39817327
0
 

Author Comment

by:ethar1
ID: 39817752
0
 

Author Comment

by:ethar1
ID: 39818226
0
 

Author Comment

by:ethar1
ID: 39818490
0
 

Author Comment

by:ethar1
ID: 39820063
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 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