Solved

# keywords

Posted on 2014-01-21
220 Views
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
Question by:ethar1
• 7

LVL 12

Expert Comment

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

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
``````

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
``````
0

Author Comment

ID: 39811242
ok, great , I will try it and come back to you
0

Author Closing Comment

ID: 39817310
thanks
0

Author Comment

ID: 39817327
0

Author Comment

ID: 39817752
0

Author Comment

ID: 39818226
0

Author Comment

ID: 39818490
0

Author Comment

ID: 39820063
0

## Featured Post

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.