Search This Blog

Words/wordCount in a text field

CREATE TABLE [log_sampladata] (
[i] [int] IDENTITY (1, 1) NOT NULL ,
[SampleData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

-- insert into dbo.log_sampladata
-- select 'a b b b ef fg hi'

-- insert into dbo.log_sampladata
-- select 'aa b bbbb bbb ef fg hi'

-- insert into dbo.log_sampladata
-- select 'aaaa bbbb bbbb bbbb ef fg hi'

SET NOCOUNT ON
DECLARE @ptrval VARBINARY(16)
DECLARE @Len int,@i int
DECLARE @word varchar(8000)
DECLARE @words table (i int identity,word varchar(8000), Cnt int default 1 )
DECLARE @start int
SET @Start = 0
SET @i = 3

SELECT @len = DATALENGTH(SampleData)+1
FROM log_sampladata
WHERE i = 3

SELECT @ptrval = TEXTPTR(SampleData)
FROM log_sampladata
WHERE i = 3

DECLARE @End int

WHILE @i <@len
BEGIN
SELECT @End = CHARINDEX(' ',SampleData,@start)
FROM log_sampladata
WHERE i = 3

If @Start > @end
GOTO A
SELECT @word = SUBSTRING(SampleData,@start,@end-@start )
FROM log_sampladata
WHERE i = 3
IF EXISTS(SELECT 1 FROM @words WHERE word = @word )
UPDATE @words
SET cnt = cnt +1
WHERE Word = @word
ELSE
INSERT INTO @words SELECT @word,1

SELECT @Start = CHARINDEX(' ',SampleData,@start)+1
FROM log_sampladata
WHERE i = 3

SELECT @i = @Start

END


A:
SELECT * FROM @words

No comments: