SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Counting Words Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MacBloscaidh
Starting Member

5 Posts

Posted - 04/01/2013 :  07:10:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/01/2013 :  07:23:53  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/01/2013 :  07:32:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/01/2013 :  07:36:55  Show Profile  Reply with Quote
Nice Visakh......

--
Chandu
Go to Top of Page

MacBloscaidh
Starting Member

5 Posts

Posted - 04/01/2013 :  09:13:16  Show Profile  Reply with Quote
Thanks guys. I'll give it a try later.

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/01/2013 :  09:23:00  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/01/2013 :  09:59:02  Show Profile  Reply with Quote
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 - 04/01/2013 :  15:20:13  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/01/2013 :  15:27:39  Show Profile  Reply with Quote

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
Go to Top of Page

MacBloscaidh
Starting Member

5 Posts

Posted - 04/01/2013 :  15:56:25  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/02/2013 :  01:23:44  Show Profile  Reply with Quote

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
Go to Top of Page

MacBloscaidh
Starting Member

5 Posts

Posted - 04/03/2013 :  13:12:16  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/03/2013 :  13:42:59  Show Profile  Reply with Quote
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 - 04/04/2013 :  01:01:07  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000