Fulltextsearch in SQL Server

Asked

Viewed 37 times

0

Hello! All right?

I need to make an appointment with fulltextsearch, because it needs to be indexed. However, I am not managing to assemble a query that returns all the possibilities, as if it were a like.

The appointment I was able to assemble is as follows

DECLARE @nmPessoa varchar(250)  

/*Exemplos de consultas*/
-- 1
--SET @nmPessoa = '1ª VARA  DE EXECUÇÃO FISCAL ESTADUAL - CURITIBA'
--2
--SET @nmPessoa = 'Curitiba'
--3
--SET @nmPessoa = 'Curi' 
--4
--SET @nmPessoa = 'FUMPISUL'
--5
--SET @nmPessoa = 'FUM'
--6
--SET @nmPessoa = 'CAMARA GRANDE'
--7
--SET @nmPessoa = 'FUMPISUL - FUNDO MUNICIPAL DE PREVIDÊNCIA DE PIRAÍ DO SUL'

    set @nmPessoa = '"*'+ @nmPessoa +'* "'  

    select p.nmPessoa
    from pessoa p       
    where CONTAINS(p.nmPessoa ,@nmPessoa) 

In the case of the first example, you must return the exact record (this is correct)

In the 2nd example all the records that have 'Curitiba' in the sentence (is correct)

Already in the 3rd example, should bring the phrases that has 'Curi', IE, the records of 'Curitiba' should also appear (It is not working)

And in example 6, it should bring the phrases that have "Camara" and "Grande", but it doesn’t work, it returns the phrases that have 'Camara Grande', as if it were only 1 word.

The consultation using like is this, but I can not use because it takes minutes to return. The amount of records is large.

DECLARE @nmPessoa varchar(250)  
DECLARE @parametro varchar(250) 
set @parametro = REPLACE( LTRIM(RTRIM(@nmPessoa)), ' ', '%')    
    select p.nmPessoa
    from pessoa p           
    where p.nmPessoa LIKE '%'+@parametro+'%'

Thanks for your help.

  • Using the replace() function in the set @parametro = REPLACE( LTRIM(RTRIM(@nmPessoa)), '', '%') generates incorrect search value, as it replaces all spaces by %. Including spaces between words. For example, "CAMARA GRANDE" turns into "CAMARA%GRANDE", resulting in the search for "LIKE '%CAMARA%GRANDE%'".

  • That’s exactly it @Josédiz, when the query is done with 'like' it returns the results I need. The problem is that it is very slow, it takes minutes to bring the results, even limiting the amount.

  • @Luciane knows the full-text search? Sounds like a better way to solve your problem

  • Hello @gmsantos, the first query I assembled is full-text search, but I couldn’t get the same results I got when I was by like.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.