Solved

keywords

Posted on 2014-01-21
9
231 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
[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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server How to create a List of Values comma separated from a TABLE? 4 44
Sorting a SQL script 5 42
SQL query 45 41
Access - convert query for SQL Server 5 17
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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 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