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, ' ', '')) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-01 : 07:32:07
|
to be safeDECLARE @Col VARCHAR(500) = ' agsgja safhajk asfhak 'SELECT 1+LEN(LTRIM(RTRIM(@col)))-LEN(REPLACE(LTRIM(RTRIM(@col)), ' ', '')) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-01 : 07:36:55
|
Nice Visakh......--Chandu |
|
|
MacBloscaidh
Starting Member
5 Posts |
Posted - 2013-04-01 : 09:13:16
|
Thanks guys. I'll give it a try later. |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 NewsGODECLARE @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. |
|
|
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 INTSELECT @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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 INTSELECT @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 MVPhttp://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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-02 : 01:23:44
|
[code]DECLARE @COUNT INTSELECT @COUNT=1+LEN(LTRIM(RTRIM(Story)))-LEN(REPLACE(LTRIM(RTRIM(Story)), ' ', '')) FROM TableNameHereSELECT @COUNT AS 'Amount Of Characters',(@COUNT/3) AS 'Divided By Three'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MacBloscaidh
Starting Member
5 Posts |
Posted - 2013-04-03 : 13:12:16
|
quote: Originally posted by visakh16
DECLARE @COUNT INTSELECT @COUNT=1+LEN(LTRIM(RTRIM(Story)))-LEN(REPLACE(LTRIM(RTRIM(Story)), ' ', '')) FROM TableNameHereSELECT @COUNT AS 'Amount Of Characters',(@COUNT/3) AS 'Divided By Three' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
visakh16, thank you very much for your help sir, very much appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-03 : 13:42:59
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 INTSELECT replace(replace(REPLACE(@col,' ','--- '),' ','-- '),' ','- ')etc... |
|
|
|