Count Number of Word - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count Number of Word

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2009-07-09 : 06:03:52

Hello sir ,

I have one table tbl_resume .
I have store all resume in one column ...RESUME

sir i have to count Number of words(Skills) used in resume :- JAVA or C++

Pls sir help me out ..............

Yaman

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-09 : 06:08:31

DECLARE @String VARCHAR(4000)
SET @String = 'JAVA SQLSERVER ORACLE .NET'

SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-09 : 06:10:08

CREATE FUNCTION [dbo].[WordCount] ( @InputString VARCHAR(MAX) )
RETURNS INT
AS
BEGIN

DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @WordCount INT

SET @Index = 1
SET @WordCount = 0

WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END

IF @PrevChar = ' ' AND @Char != ' '
SET @WordCount = @WordCount + 1

SET @Index = @Index + 1
END

RETURN @WordCount

END
GO

SELECT DBO.WORDCOUNT('JAVA SQLSERVER ORACLE .NET')

-------------------------
R..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 06:38:12

Also refer http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/21/count-number-of-words-in-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

nereynolds1
Starting Member

1 Post

Posted - 2014-05-30 : 21:26:46

This is a little cumbersome but it handles the whitespace issue nicely, its fast and inline, no udf.

DECLARE @Term VARCHAR(100) = ' this is pretty fast '

SELECT @Term, LEN(REPLACE(REPLACE(REPLACE(' '+@Term,' ',' '+CHAR(1)) ,CHAR(1)+' ',''),CHAR(1),'')) - LEN(REPLACE(REPLACE(REPLACE(REPLACE(' '+@Term,' ',' '+CHAR(1)) ,CHAR(1)+' ',''),CHAR(1),''),' ','')) [Word Count]

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-31 : 00:39:55

I think this answers your Question perfectly


declare @S varchar(100) = 'May be I was newbie to JAVA but i can say C++ is not efficient as JAVA.'
SELECT * INTO #temp
FROM (
SELECT Parent1.Child1.value('.','VARCHAR(50)') Col FROM 
(SELECT CAST('<r>'+REPLACE(@S,' ','</r><r>')+'</r>' AS XML) Child2) Parent2
CROSS APPLY Parent2.Child2.nodes('r') AS Parent1(Child1)
)a
SELECT * FROM #temp
SELECT           COUNT(CASE WHEN Col LIKE '%JAVA%' THEN 1 END)  [JavaWordCount]
		,COUNT(CASE WHEN Col COLLATE Latin1_General_100_CS_AS_WS LIKE '%C++%' THEN 1 END) [CWordCount]
FROM #temp 
DROP TABLE #temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources