Words in a string

Question

Post reply

Words in a string

Ramesh Mandava

SSC Rookie

Points: 47
More actions
May 12, 2006 at 4:02 pm

#169511

How do you determine the number of words in a string?
eg
Name1 : Robert Duvall Jr
Name2 : Tom Clancy
Name3 : Fishbourne
I need to retrieve names with more than one word.
I am assuming we can check for the number of spaces,but is there a predefined function ?
I used charindex,but it gives first location not all.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

If you just want strings with one or more spaces you could use something akin to:

...

WHERE CHARINDEX(' ', column_name) > 0

If you want an exact count of the number of words (i.e. blank spaces + 1 assumedly) you'd have to get a little more creative.

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 2

evaluate the length of the original string.

create a temp variable with all spaces removed/replaced with null from the original content.

evaluate the length of the temp string.

the difference + 1 is the number of words.

this can be done in one step...have a go!

above assumes no double-spaces between words/punctuation...if this is an issue....evaluate for for double spaces 1st.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Here's one way.. also assumes no double spaces are present...

-- Sample on how to count # of occurences of a single char

-- or pattern from a text source. Note that if (space) is counted

-- in order to find the number of 'words', + 1 must be added to the

-- result to denote the correct word-count.

declare @pattern varchar(8000), @source varchar(8000)

select @pattern = 'some', @source = 'some string with some text in it'

select (datalength(@source) - datalength(replace(@source, @pattern, ''))) / datalength(@pattern) as 'patCount'

/Kenneth

David Burrows SSC Guru Points: 65142 More actions · Answer 4

If single spaced

SELECT LEN(@anystring) - LEN(REPLACE(@anystring,' ','')) + 1

(use LTRIM if required to remove leading spaces)

If the number spaces between the words cannot be guaranteed then use the now famous Numbers table

SELECT COUNT(*)

FROM [Numbers]

WHERE number BETWEEN 2 AND LEN(@anystring)+1

AND SUBSTRING(' '+@anystring,number,1) <> ' '

AND SUBSTRING(' '+@anystring,number-1,1) = ' '

Far away is close at hand in the images of elsewhere.
Anon.

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Hi all,

Yet another way!

It deals with all the different types of test data I can think of...

--data

declare @t table (v varchar(8000))

insert @t

select null

union all select ' '

union all select 'a'

union all select 'a '

union all select ' a'

union all select ' a '

union all select 'some string with some text in it'

union all select ' some string with some text in it '

--calculation

select v, tidiedv,

case when isnull(v, '') = '' then 0

else len(tidiedv) - len(replace(tidiedv, ' ', '')) + 1

end as words

from (

select v, rtrim(ltrim(replace(replace(replace(v, ' ', ' ¬'), '¬ ', ''), '¬', '')))

as tidiedv from @t

) a

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Ramesh Mandava SSC Rookie Points: 47 More actions · Answer 6

Thanks Eone!!

You guys really helped me figure this thing out. The data I am dealing with has only 1 space,but will keep the rest of the advice in mind.

Thanks Again

Ramesh Mandava SSC Rookie Points: 47 More actions · Answer 7

Ryan,

Quick question.

Was wondering what " ' ¬' "is?

I am relatively new sql,so pardon me if it is a stupid question.

Thanks

RyanRandall SSChampion Points: 13623 More actions · Answer 8

Hi Ramesh,

No worries.

'¬' is just a somewhat unusual keyboard character (it's on the key under 'Esc' on my keyboard). You can run this to select it...

select char(172)

I should've mentioned that for the technique I gave to work, your text should not contain that character.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.