Check if a string in SQL contains at least 2 words

Question

I have a sql query, for example:

SELECT * FROM table WHERE lower(title) LIKE lower('%It a beautiful string i think%') 

I need to check if at least 2 words are in my string It a beautiful string i think in the header of my field ... How can I do this?

For example, if there is a line I think it beautiful in my field header, this request should return this object to me ...

Thanks!

Answer 1

You can split the row into a temporary table (say, using something like this: http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql/ ), and then connect, with the bill.

Answer 2

You can generate the following SQL statement dynamically:

 SELECT title, count(*) FROM ( SELECT title FROM table1 WHERE (' ' + lower(title) + ' ') LIKE lower('% It %') UNION ALL SELECT title FROM table1 WHERE (' ' + lower(title) + ' ') LIKE lower('% s %') UNION ALL SELECT title FROM table1 WHERE (' ' + lower(title) + ' ') LIKE lower('% a %') UNION ALL SELECT title FROM table1 WHERE (' ' + lower(title) + ' ') LIKE lower('% beautiful %') UNION ALL SELECT title FROM table1 WHERE (' ' + lower(title) + ' ') LIKE lower('% string %') UNION ALL SELECT title FROM table1 WHERE (' ' + lower(title) + ' ') LIKE lower('% I %') UNION ALL SELECT title FROM table1 WHERE (' ' + lower(title) + ' ') LIKE lower('% think %') ) AS table2 GROUP BY title HAVING COUNT(*) >= 2 

A stored procedure can be more efficient, and you could do all the server-side work.

Answer 3

You can use such a function

 CREATE FUNCTION [dbo].[CheckSentece] (@mainSentence varchar(128), @checkSentence varchar(128)) RETURNS NUMERIC AS BEGIN SET @mainSentence=LOWER(@mainSentence) SET @checkSentence=LOWER(@checkSentence) DECLARE @pos INT DECLARE @word varchar(32) DECLARE @count NUMERIC SET @count=0 WHILE CHARINDEX(' ', @checkSentence) > 0 BEGIN SELECT @pos = CHARINDEX(' ', @checkSentence) SELECT @word = SUBSTRING(@checkSentence, 1, @pos-1) DECLARE @LEN NUMERIC //Simple containment check, better to use another charindex loop to check each word from @mainSentence SET @LEN=(SELECT LEN(REPLACE(@mainSentence,@word,''))) if (@LEN<LEN(@mainSentence)) SET @ count=@count +1 SELECT @checkSentence = SUBSTRING(@checkSentence, @pos+1, LEN(@checkSentence) -@pos ) END return @count END 

and get the number of words from the second sentence contained in the first