Question: How to select strings with less than three words?

For example:

```none
id keyword
1  my name is john
2  im mike
3  david cameron
4  electra
5  goliath is my name
6  michael jordan

```

So i want to select the keyword column with less than 3 word count, it should be :

```none
im mike
david cameron
electra
michael jordan

```

Answer: 
Try:

```
select *
  from table_name
 where length(keyword) - length(replace(keyword, ' ', '')) + 1 < 3

```

This takes the length of the whole string minus the length of the string without spaces (calculating the # of spaces), and then adds one (ie. if a string has one space that means there are 2 words not 1 word), and filters out any rows where the calculation is not < 3
Using [Jeff Moden's 8K Tally-Ho CSV splitter](http://www.sqlservercentral.com/articles/Tally+Table/72993/)

```
create function [dbo].[DelimitedSplit8K](
   @pString varchar(8000) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
  ,@pDelimiter char(1)
)
returns table with schemabinding as
return
  with E1(N) AS ( 
    select N from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) )E1(N)
  ),                                             --10E+1 or 10 rows
  E2(N) as (select 1 from E1 a cross join E1 b), --10E+2 or 100 rows
  E4(N) as (select 1 from E2 a cross join E2 b), --10E+4 or 10,000 rows max
  cteTally(N) as (
    select top (isnull(datalength(@pString),0))
      row_number() over (order by (select null)) 
    from E4
  ),
  start(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
    select 1 union all
    select t.N+1 from cteTally t where substring(@pString,t.N,1) = @pDelimiter
  ),
  Len(N1,L1) as(--==== Return start and length (for use in substring)
    select s.N1,
        isnull(nullif(charindex(@pDelimiter,@pString,s.N1),0) - s.N1,8000)
    from start s
  )
  --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
  select
    ItemNumber = row_number() over(order by l.N1),
    Item       = substring(@pString, l.N1, l.L1)
  from Len l
;
go

```

and sample data as suggested in a CTE

```
with 
data as (
  select id,keyword from (values
     --id   keyword
     (1,  'my name is john')
    ,(2,  'im mike')
    ,(3,  'david cameron')
    ,(4,  'electra')
    ,(5,  'goliath is my name')
    ,(6,  'michael jordan')
    ,(7,  'michael   jordan')
  )data(id,keyword)
),

```

this SQL

```
strings as (
  select
     data.id
    ,keyword
    ,s.Item
    ,s.ItemNumber
  from data
  cross apply dbo.DelimitedSplit8K(data.keyword,' ') as s
)

select
    strings.id
from strings
where len(strings.item) > 0  -- remove zero-length string and thus doubled-up delimters
group by strings.id
having count(itemnumber) <=3;

```

yields as desired:

```
id
-----------
2
3
4
6
7

```

Note that the doubled-up spaces in id=7 are compressed by the removal of zero-length strings.