CODEWITHSUNDEEP
sqlsql-like
Clément Andraud
Clément Andraud

Reputation: 9279

Test if a string contains at least 2 words in SQL

I have an sql request like : 

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

I need to check if at least 2 words in my string It's a beautiful string i think are contained in my field title... How can i do that ?

For example, if in my field title i have the string I think it's beautiful, This query should return me this object...

Thanks !

Upvotes: 4

Views: 3770

Answers (3)

Alex
Alex

Reputation: 8937

You could use a function like this 

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 second sentence contained in the first one

Upvotes: 0

Tarik
Tarik

Reputation: 11209

You could 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 might be more efficient and you could have the whole job done on the server side.

Upvotes: 0

Ryan
Ryan

Reputation: 3982

You could split your string into a temporary table (say, using something like this: http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql/) and then do a join, with a count.

Upvotes: 2

Related Questions