Solved

keywords

Posted on 2014-01-21
9
234 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Closing Comment

by:ethar1
ID: 39817310
thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

615 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