Solved

keywords

Posted on 2014-01-21
9
220 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 12

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
 

Author Closing Comment

by:ethar1
ID: 39817310
thanks
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now