coutning number of workd in a nvarchar cell

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

SQL Server 2000 Forums

SQL Server Development (2000)

coutning number of workd in a nvarchar cell

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2005-12-28 : 06:23:53

i want to count the number of words in a colum of type nvarchar(250)
o know how many word it contains and which of the cels contain more then 2 words
how can i do this?
(of course the words are seperated with space)
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-28 : 06:34:29

Here is one of the ways

Declare @n nvarchar(2500)
Set @n='This is to test how many words this variable has'
select len(@n)-len(replace(@n,' ',''))+1 as No_of_Words

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-01-01 : 14:39:17

nice trick:)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-01-03 : 08:35:03

but what if you have 2 spaces? it will count more then needed!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 08:44:09

[code]
Declare @n nvarchar(2500)
Set @n='This is to test how many words this variable has'
set @n=Replace(Replace(Replace(@n,' ',' ^'),'^ ',''),'^','')
select len(@n)-len(replace(@n,' ',''))+1 as No_of_Words
[/code]

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-01-03 : 15:58:30

can u explain what going on?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-03 : 18:01:28

Modified to allow for leading/trailing spaces and to add comments.


declare @n varchar(8000)
Set @n= '     This is to test          how many ' +
	'           words this variable has '

-- Replace all multiple spaces with a single space
-- by nesting REPLACE functions.
-- Works with strings of up to 8000 characters
set @n = 
	replace(replace(replace(replace(replace(
	replace(ltrim(rtrim(@n)),replicate(' ',87),' ')
	,'            ',' '),'    ',' '),'   ',' ')
	,'  ',' '),'  ',' ')

-- Count the number of spaces in the string
select len(@n)-len(replace(@n,' ',''))+1 as No_of_Words

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 00:20:39

Well. Trailing spaces are automatically omitted. So only leading spaces are to be considered

Here is the new approach


Declare @n nvarchar(2500)
Set @n= '     This is to test          how many ' +
	'           words this variable has '

set @n=Replace(Replace(Replace(@n,' ',' ^'),'^ ',''),'^','')

select len(@n)-len(replace(@n,' ',''))+case when left(@n,1)=' ' then 0 else 1 end as No_of_Words

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-01-04 : 01:06:37

what does the ' ^' means?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 01:19:58

>>what does the ' ^' means?

It doesnt mean anything. It is just used as temporary character to replace space. You can use # or ~ as well

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-04 : 10:36:49

quote:
Originally posted by madhivanan

Well. Trailing spaces are automatically omitted. So only leading spaces are to be considered

Here is the new approach

Declare @n nvarchar(2500)
Set @n= ' This is to test how many ' +
' words this variable has '

set @n=Replace(Replace(Replace(@n,' ',' ^'),'^ ',''),'^','')

select len(@n)-len(replace(@n,' ',''))+case when left(@n,1)=' ' then 0 else 1 end as No_of_Words

Madhivanan

Failing to plan is Planning to fail

It does not work in all cases. The Replace(@n,' ',' ^') can exceed the length of the string and give the wrong results. See example below:


Declare @n nvarchar(2500)
Set @n= '     This is to test' +replicate(' ',2000)+
	'how many ' +
	'           words this variable has '

set @n=Replace(Replace(Replace(@n,' ',' ^'),'^ ',''),'^','')

select len(@n)-len(replace(@n,' ',''))+case when left(@n,1)=' ' then 0 else 1 end as No_of_Words


No_of_Words 
----------- 
4

(1 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-05 : 01:16:43

Well. It works if you change nvarchar to varchar


Declare @n varchar(2500)
Set @n= '     This is to test' +replicate(' ',2000)+
	'how many ' +
	'           words this variable has '


set @n=Replace(Replace(Replace(@n,' ',' ^'),'^ ',''),'^','')

select len(@n)-len(replace(@n,' ',''))+case when left(@n,1)=' ' then 0 else 1 end as No_of_Words

But still it doesnt work other cases like varchar(7500) and replicate(' ',7000)
Yours is best approach

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-01-05 : 01:24:17

why is there a diffrence between the cases of using varchar and nvarchar?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources