Search Word Combination in SQL (Redshift)

Asked

Viewed 43 times

1

Dear friends, good afternoon.

I have a table that stores the texts of messages exchanged by email and I need to identify the emails that comes with a set of words, for example:

inserir a descrição da imagem aqui

For any message that displays the combinations ['Merged' and 'closed'], ['merged' and 'Closed'], ['Merged' and 'closed'] would identify as 1.

I tried the following query:

select distinct
ticket_id,
1 as mesclado 
FROM conversation c 
WHERE
((body_text LIKE ('%Mesclado%') OR body_text LIKE ('%mesclado%')) and body_text LIKE ('%fechado%'))
OR ((body_text LIKE ('%merged%') OR body_text LIKE ('%Merged%')) and body_text LIKE ('%closed%')) 
OR ((body_text LIKE ('%mesclados%') OR body_text LIKE ('%Mesclados%')) and body_text LIKE ('%fechado%'))

The expected result would be a table in the following configuration:

inserir a descrição da imagem aqui

sql
  • 1

    What result do you expect from yours query? Add a practical example in http://sqlfiddle.com/

  • The result was not as expected? It would not be simpler to do, for example, WHERE
(lower(body_text) LIKE '%mesclado%' and lower(body_text) LIKE '%fechado%')
OR (lower(body_text) LIKE '%merged%' and lower(body_text) LIKE '%closed%') 
OR (lower(body_text) LIKE '%mesclados%' and lower(body_text) LIKE '%fechado%')? Or, depending on the DBMS used, work with regular expressions? If it will always be listed only the merged field with value 1 why list it?

1 answer

1

You can use a direct test in the query and already bring the status, because the way you are doing, it makes no sense to have the column mesclado, and will have to make several queries to list all status, and if wanted, make a filter right on it.

Consultation bringing status:

SELECT ticket_id,
 (CASE WHEN (body_txt LIKE 'Mesclado e fechado%' OR body_txt LIKE 'Merged and closed%') THEN 1 ELSE
 (CASE WHEN (body_txt LIKE 'Fechado e mesclado%' OR body_txt LIKE 'Closed and merged%') THEN 2 ELSE 3) 
 END) END) as status
FROM conversation

Query filtering status:

SELECT * FROM 
 (SELECT ticket_id,
     (CASE WHEN (body_txt LIKE 'Mesclado e fechado%' OR body_txt LIKE 'Merged and closed%') THEN 1 ELSE
     (CASE WHEN (body_txt LIKE 'Fechado e mesclado%' OR body_txt LIKE 'Closed and merged%') THEN 2 ELSE 3) 
     END) END) as status
    FROM conversation)
WHERE status = 1

Browser other questions tagged sql

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