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
 Counting Words Query

Author  Topic 

MacBloscaidh
Starting Member

5 Posts

Posted - 2013-04-01 : 07:10:13
Hi,

Is there a query that will allow me to return a count of words from a particular column?

I have a column with a Data Type of varchar(max)

I want to be able to count how many separate words are in this field.

Any help appreciated

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 07:23:53
DECLARE @Col VARCHAR(500) = 'agsgja safhajk asfhak'
SELECT 1+LEN(@col)-LEN(REPLACE(@col, ' ', ''))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 07:32:07
to be safe

DECLARE @Col VARCHAR(500) = ' agsgja safhajk asfhak '
SELECT 1+LEN(LTRIM(RTRIM(@col)))-LEN(REPLACE(LTRIM(RTRIM(@col)), ' ', ''))


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 07:36:55
Nice Visakh......

--
Chandu
Go to Top of Page

MacBloscaidh
Starting Member

5 Posts

Posted - 2013-04-01 : 09:13:16
Thanks guys. I'll give it a try later.

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 09:23:00
quote:
Originally posted by MacBloscaidh

Thanks guys. I'll give it a try later.


welcome..


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 09:59:02
quote:
Originally posted by bandi

Nice Visakh......

--
Chandu


Thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MacBloscaidh
Starting Member

5 Posts

Posted - 2013-04-01 : 15:20:13
quote:
Originally posted by bandi

quote:
Originally posted by MacBloscaidh

Thanks guys. I'll give it a try later.


welcome..


--
Chandu



Hi there.

The query worked fine, however I meant to add - is there a way in which I can store the returned value in a variable?

I know how to declare a variable (counting characters, not words) and store a value as:

USE News
GO
DECLARE @COUNT INT = LEN ('Counting Characters')
SELECT @COUNT AS 'Amount Of Characters',
(@COUNT/3) AS 'Divided By Three';


However I don't know how to take the value from your original query and store it.

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 15:27:39
[code]
DECLARE @Col VARCHAR(500) = ' agsgja safhajk asfhak '
DECLARE @COUNT INT

SELECT @COUNT=1+LEN(LTRIM(RTRIM(@col)))-LEN(REPLACE(LTRIM(RTRIM(@col)), ' ', ''))

SELECT @COUNT AS 'Amount Of Characters',
(@COUNT/3) AS 'Divided By Three'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MacBloscaidh
Starting Member

5 Posts

Posted - 2013-04-01 : 15:56:25
quote:
Originally posted by visakh16


DECLARE @Col VARCHAR(500) = ' agsgja safhajk asfhak '
DECLARE @COUNT INT

SELECT @COUNT=1+LEN(LTRIM(RTRIM(@col)))-LEN(REPLACE(LTRIM(RTRIM(@col)), ' ', ''))

SELECT @COUNT AS 'Amount Of Characters',
(@COUNT/3) AS 'Divided By Three'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks again -

The bit in red - I can get the info / amount of words from whatever I type in here - but how would I get this info from a table that's already created with a field that already has data in it?

I want to take the data from a column called "Story" and from that, calculate the amount of words.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-02 : 01:23:44
[code]
DECLARE @COUNT INT

SELECT @COUNT=1+LEN(LTRIM(RTRIM(Story)))-LEN(REPLACE(LTRIM(RTRIM(Story)), ' ', '')) FROM TableNameHere

SELECT @COUNT AS 'Amount Of Characters',
(@COUNT/3) AS 'Divided By Three'
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MacBloscaidh
Starting Member

5 Posts

Posted - 2013-04-03 : 13:12:16
quote:
Originally posted by visakh16


DECLARE @COUNT INT

SELECT @COUNT=1+LEN(LTRIM(RTRIM(Story)))-LEN(REPLACE(LTRIM(RTRIM(Story)), ' ', '')) FROM TableNameHere

SELECT @COUNT AS 'Amount Of Characters',
(@COUNT/3) AS 'Divided By Three'



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




visakh16, thank you very much for your help sir, very much appreciated.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-03 : 13:42:59
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-04-04 : 01:01:07
Does not work if there is more than one space between the words. You might want to replace ' ', ' ' and ' ' etc with something else until you are confident you've got them all. That's the simplest but not ideal.

DECLARE @Col VARCHAR(500) = ' agsgja safhajk asfhak '
DECLARE @COUNT INT

SELECT replace(replace(REPLACE(@col,' ','--- '),' ','-- '),' ','- ')

etc...
Go to Top of Page
   

- Advertisement -