SELECT per word in sql server without using Full Text Index

Question

SELECT per word in sql server without using Full Text Index

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I need to query a table with the following words:

"Today was a rainy day"

I need to search for any of these words. For example if you found the word "day" , you would have to return the record. That is, any of those words that I think he would have to return to me.

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

"Rainy day today"

If the record in this table has these three words, it does not necessarily have to be in that order.

I'm breaking my head how to do this. Oh I can not use FUllText because I would have to ask for authorization until PAPA because this feature is disabled in Production. I am using SQL SERVER 2008 R2.

Thanks for the replies I was able to make some progress, I did more or less creating a temporary table to play my search string and separated 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 Until then OK. But it confuses the word (day by diagnosis) this in function of the use of Like. But 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.

They 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

I'm basically taking TABLE A (TEMPORARY) and comparing it to table B and returning the records of table B according to the criteria I've spoken

sql sql-server

asked by anonymous 03.03.2017 / 17:55

2 answers

0

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

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

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

03.03.2017 / 19:46

Use foreign key as primary key - Hibernate Scrolling through a file and splitting its contents into separate files using Python

score 2 · Accepted Answer

According to this response from the StackOverflow

Since you can not use Full text, this should help

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

EDIT
I gave a larger search and I think that solves your problem change [tabela] by your table and [campo] by your 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]%'