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

1 comment:

Alwin Co Daan said...

This is an amazing blog,it gives very helpful messages to us.Besides that Wisen has established as Best Dot Net Training in Chennai. or learn thru ASP.NET Online Training . Nowadays Dot Net has tons of job opportunities on various vertical industry.