SELECT by word in sql server without using Full Text Index

Question

I need to make a query in a table with the following words:

"Today the day got rainy"

I need to search for any of these words. For example if you found the word "day", would have to return the record to me. I mean, any of these words you find he would have to return to me.

I also need to do a survey that returns all these searched words for example:

"rainy day today"

if the record of this table has these three words not necessarily in that order would have to bring.

I’m racking my brain on how to do this. Oh I can’t use the FUllText because I would have to ask for authorization until pro PAPA because this feature is disabled in Production. I am using SQL SERVER 2008 R2.

---

Thanks for the answers I managed to advance a little bit so I did more or less creating a temporary table to play my search string and separating the words by space. but I’m still in trouble

SET NOCOUNT ON

DECLARE @ARRAY VARCHAR(8000), @DELIMITADOR VARCHAR(100), @S VARCHAR(8000)

-- VALORES PASSADOS PARA A VARIAVEL @ARRAY
SELECT @ARRAY = 'hoje o dia ficou chuvoso'
-- SETANDO O DELIMITADOR
SELECT @DELIMITADOR = ' '

IF LEN(@ARRAY) > 0 SET @ARRAY = @ARRAY + @DELIMITADOR 
CREATE TABLE #ARRAY(ITEM_ARRAY VARCHAR(8000))

WHILE LEN(@ARRAY) > 0
BEGIN
   SELECT @S = LTRIM(SUBSTRING(@ARRAY, 1, CHARINDEX(@DELIMITADOR, @ARRAY) - 1))
   INSERT INTO #ARRAY (ITEM_ARRAY) VALUES (@S)
   SELECT @ARRAY = SUBSTRING(@ARRAY, CHARINDEX(@DELIMITADOR, @ARRAY) + 1, LEN(@ARRAY))
END




-- Qualquer uma das palavras
SELECT  DISTINCT * FROM TABELA_A a JOIN #ARRAY ON CAMPO1 like '%' + ITEM_ARRAY + '%';

DROP TABLE #ARRAY

SET NOCOUNT OFF

In this Example it returns any of the words that are in my temporary table So far OK. But he confuses the word (day by day) this in function of the use of the Like. However, I do not know how to replace it. Moreover I could not return ONLY the records that contain all the words that are in the temporary table.

There are two things. First: Return any of the words that are in the temporary table Second: Return all words that are in the temporary table

Basically I’m taking TABLE A (TEMPORARIA) and comparing with Table B and returning the records of Table B according to the criteria I mentioned

Answer 1

According to that reply from Stackoverflow

Since you cannot use Full text, this should help

SELECT * FROM table WHERE '.' + column + '.' LIKE '%[^a-z]parameters[^a-z]%'

EDIT
I did a little more digging and I think that solves your problem change [tabela] by its table and [campo] by his field

declare @List NVARCHAR(MAX), @Delim CHAR(1)
set @List = 'Hoje o Dia ficou chuvoso' 

set @Delim = ',' 
set @List = REPLACE(@List, ' ', @Delim)

SELECT      * 
FROM        [tabela] 
INNER JOIN  (
    -- USED TO SPLIT THE VALUES INTO A NEW TABLE
        SELECT 
            [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
            CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
            FROM sys.all_objects) AS x
            WHERE Number <= LEN(@List)
            AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
) B ON  '.' + [campo] + '.' LIKE '%[^a-z]'+ B.VALUE +'[^a-z]%'

Answer 2

TABLE = sua tabela, FIELD = campo que deseja filtrar, keyword = palavras chaves(chuvoso, dia ,...).

Remembering that % replaces the rest of the sentence, and so has to be put before and after the keyword.

SELECT FIELD1, FIELD2, FIELD3 
FROM TABLE 
WHERE 
  FIELD4 LIKE '%KEYWORD1%' OR 
  FIELD4 LIKE '%keyWord2%'