counting word lengths - 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

counting word lengths

Author

Topic

marcusstar10
Starting Member

14 Posts

Posted - 2011-03-10 : 11:01:12

how do i select words columns from a table that only have 2 words and then count the length of the second word

i know the count function works but i dont know how to select only 2 words from a column and return the length of the second word

select count(code_name) from missions;

any input is greatly appreciated

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 11:13:36

Could you supply some data and expected results?

Jim

Everyday I learn something that somebody else already knew

revdnrdy
Posting Yak Master

220 Posts

Posted - 2011-03-10 : 11:18:12

Here is what I would do..

Look for the index of the first whitespace which will exist between the two words.

Then use the substring function (SUBSTR) to count every character after that whitespace.

Definitely would make use of the string functions (refer to link).
http://msdn.microsoft.com/en-us/library/ms181984.aspx

This task I would use CHARINDEX, LEFT, RIGHT, SUBSTR and perhaps others

r&r

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 11:29:44

Could you supply some sample data and expected results?

Jim

Everyday I learn something that somebody else already knew

marcusstar10
Starting Member

14 Posts

Posted - 2011-03-10 : 11:31:40

Theses are the columns, the first is id and the second is code_name and the table is called missions

201 High Mountain
202 impedimenta
203 overpowering
204 tambour

SELECT LENGTH(code_name) FROM missions;

counts the total amount of words in the column, but i need to beable to the second word

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 11:53:41

This only works if the are less than 3 words in code_name, as indicated by your sample data
DECLARE @table TABLE(id int ,code_name varchar(50))
INSERT INTO @table
SELECT 201,'High Mountain' UNION
SELECT 202,'impedimenta b c' UNION
SELECT 203,'overpowering' UNION
SELECT 204,'tambour'

SELECT code_name,len(parsename(replace(code_name,' ','.'),1) )

FROM @table

WHERE LEN(code_name) = LEN(replace(code_name,' ','')) + 1

Jim

Everyday I learn something that somebody else already knew

marcusstar10
Starting Member

14 Posts

Posted - 2011-03-10 : 12:45:09

unfortunately the code doesnt work i get the following error message

ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 300 Column: 64

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-10 : 12:49:41

It works for me. Are you using Microsoft SQL Server?

Jim

Everyday I learn something that somebody else already knew

marcusstar10
Starting Member

14 Posts

Posted - 2011-03-10 : 12:58:30

No sorry i should have mentioned im using SQL ORACLE developer

aleximas
Starting Member

11 Posts

Posted - 2011-03-11 : 00:03:54

Look for the index of the first whitespace which will exist between the two words.
unspammed