'Simple' SQL Search query

Question

Ok, so I'm working on a basic search SPROC. One of the Parameters is a search text (this will be the test the user enters in, words separated by spaces etc.)

Now all I need is to search these words on a single column in a table, BUT I want it to have ALL the keywords that were entered (at the moment all I can do is if 1 of them is there)

So is there a special SQL command that allows me to do this?

Answer 1

You need to split the @SEARCH_TEXT into the words and ideally store it in a temporary table @FILTER_TABLE with one column WORD containing the words. You can google for "sql comma split ...", but answer to this question might be helpful. This is also interesting.

Then you just use JOIN in your query to filter the rows. The simplest query then that would return all the matches would be:

SELECT  t.*
        f.WORD
FROM    MyTable t
JOIN    @FILTER_TABLE f
    ON t.MyColumn = f.WORD --// = or LIKE operator

But if you provide an example of your data and expected result, people would could be more helpful.

Answer 2

SELECT * FROM `mytable` WHERE `value` LIKE '%foo%' AND `value` LIKE '%bar%'

for each word you create another AND...

Answer 3

You can try something like this

check the occurances of the words required, and compare to the count of split words.

All issue i forsee is matches to partial words, but this might get you started

/*
ALTER FUNCTION [dbo].[SplitString]
(
     @String VARCHAR(8000) ,
     @Delimiter VARCHAR(10)
)
RETURNS @RetTable TABLE(
     String varchar(1000)
)
AS 
BEGIN
    DECLARE @i INT ,
      @j INT
    SELECT  @i = 1
    WHILE @i <= LEN(@String)
    BEGIN
     SELECT @j = CHARINDEX(@Delimiter, @String, @i)
     IF @j = 0
     BEGIN
      SELECT @j = LEN(@String) + 1
     END
     INSERT @RetTable SELECT SUBSTRING(@String, @i, @j - @i)
     SELECT @i = @j + LEN(@Delimiter)
    END
    RETURN
END
*/

DECLARE @SearchString VARCHAR(MAX)

SELECT @SearchString = 'your,of'

DECLARE @SearchStringTable TABLE(
     Words VARCHAR(MAX)
)

DECLARE @TABLE TABLE(
     Col VARCHAR(MAX)
)

INSERT INTO @TABLE (Col)
SELECT 
'On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document.'
INSERT INTO @TABLE (Col)
SELECT 
'You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks.'
INSERT INTO @TABLE (Col)
SELECT 
'When you create pictures, charts, or diagrams, they also coordinate with your current document look.'

INSERT INTO @SearchStringTable (Words) SELECT * FROM dbo.SplitString(@SearchString,',')

SELECT  t.Col,
     COUNT(1) AS Number
FROM    @TABLE t,
     @SearchStringTable s
WHERE   CHARINDEX(s.Words,t.Col) > 0
GROUP BY t.Col
HAVING  COUNT(1) = (SELECT COUNT(1) FROM @SearchStringTable)