strings - determining if a string has more than one word?

Question

Post reply

strings - determining if a string has more than one word?

Trystan Hughes

SSCrazy

Points: 2109
More actions
April 27, 2006 at 10:50 am

#168750

Hi all,
I have a need where I am required to determine whether a string contains more than one word. If the string contains one word I don't want to do anything, but if it conatians more than one word, I need to do something.
Whats the best way for me to tackle this?
Thanks
Tryst

Viewing 5 posts - 1 through 4 (of 4 total)

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

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

Take a look at PATINDEX:

http://msdn2.microsoft.com/en-us/library/ms188395.aspx

and CHARINDEX:

http://msdn2.microsoft.com/en-us/library/ms188395.aspx

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

If the words in your string are separated by spaces then you can use charindex (as Pam suggested)..like this:

DECLARE @String1 VarChar(20)
DECLARE @String2 VarChar(20)
SET @String1 = 'OneWord'
SET @String2 = 'Two Words'
IF CHARINDEX(SPACE(1), @String1) = 0
   PRINT @String1 + ' is one word'
IF CHARINDEX(SPACE(1), @String2) > 0
   PRINT @String2 + ' are two words.'

**ASCII stupid question, get a stupid ANSI !!!**

rfrancisco Mr or Mrs. 500 Points: 544 More actions · Answer 3

Here's a user-defined function that counts the number of words in a given string:

http://www.sql-server-helper.com/functions/count-words.aspx

http://www.sql-server-helper.com

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 4

Not a bad function but it takes more than 4 minutes to execute on a mere million rows where each row contains a six word value...

The following function, butt ugly and brute force as it is, runs in less than a minute on the same record set...

CREATE FUNCTION dbo.WordCount (@InputString VARCHAR(8000))

RETURNS SMALLINT

AS

BEGIN

SELECT @InputString = LTRIM(RTRIM(

REPLACE(

@InputString

,SPACE(17),' ')

,SPACE(13),' ')

,SPACE(11),' ')

,SPACE( 7),' ')

,SPACE( 5),' ')

,SPACE( 3),' ')

,SPACE( 2),' ')

))

RETURN ( ISNULL(NULLIF(LEN(@InputString),0),-1)

- ISNULL(LEN(REPLACE(@InputString,' ','')),0)

+1)

END

...and, yes, that's even faster than the Tally (Numbers) table solution which takes more than a minute and a half and looks like this...

CREATE FUNCTION dbo.WordCount (@InputString VARCHAR(8000))

RETURNS SMALLINT

AS

BEGIN

DECLARE @MyLength SMALLINT

SET @InputString = ' '+@InputString

SET @MyLength = LEN(@InputString)

RETURN (

SELECT COUNT(*)

FROM Tally

WHERE N BETWEEN 2 AND @MyLength

AND SUBSTRING(@InputString,N,1) >' '

AND SUBSTRING(@InputString,N-1,1)=' '

)

END

However... all of these functions count words... the original request was to just determine if there was more than one word.

Sushila came the closest, so far but, as the following example shows, if there are leading spaces, it won't find the one word example...

DECLARE @String1 VarChar(20)DECLARE @String2 VarChar(20)

SET @String1 = ' LeadingSpace'

SET @String2 = ' Leading Space'

IF CHARINDEX(SPACE(1), @String1) = 0

PRINT @String1 + ' is one word'

IF CHARINDEX(SPACE(1), @String2) > 0

PRINT @String2 + ' are two words.'

To borrow on her fine and very fast example, the following should guarantee a correct return (1 word or more than 1 word) regardless of the configuration of the spaces...

DECLARE @String1 VarChar(20)DECLARE @String2 VarChar(20)

SET @String1 = ' LeadingSpace'

SET @String2 = ' Leading Space'

IF @String1 NOT LIKE '%[^ ]%[ ]%[^ ]%'

AND @String1 LIKE '%[^ ]%'

PRINT @String1 + ' is one word'

IF @String2 LIKE '%[^ ]%[ ]%[^ ]%'

PRINT @String2 + ' are two words.'

...and it will do it at a rate of one million rows in only 2-10 seconds when used in a form similar to either of the following ("regular expressions")...

--===== Find all rows with more than 1 word

SELECT somecolumn

FROM sometable

WHERE somecolumn LIKE '%[^ ]%[ ]%[^ ]%'

--===== Find all rows with just 1 word and is not all blanks and is not an empty string

SELECT somecolumn

FROM sometable

WHERE somecolumn NOT LIKE '%[^ ]%[ ]%[^ ]%'

AND somecolumn LIKE '%[^ ]%'

Of course, and borrowing on Sushila's code again, you could also do it this way...

DECLARE @String1 VarChar(20)DECLARE @String2 VarChar(20)

SET @String1 = ' LeadingTrailingSpace '

SET @String2 = ' LeadingTrailing Space '

IF CHARINDEX(SPACE(1), LTRIM(RTRIM(@String1))) = 0

PRINT @String1 + ' is one word'

IF CHARINDEX(SPACE(1), LTRIM(RTRIM(@String2)))) > 0

PRINT @String2 + ' are two words.'

Hope this helps...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)