SQL Query with multiple arguments

Question

SQL Query with multiple arguments

Navigation

#1 by (1 votes)

1

I need to create an SQL query that may or may not have multiple arguments. Example:

Pesquisa 1: "João Goiânia"
Pesquisa 2: "Goiânia João"
Pesquisa 3: "João Advogado Goiânia"

The query should go through three fields: name, city, profession. Since the arguments can be in one, two or all of them.

Imagining a populated table:

id, nome, cidade, profissão
1, Maria, Brasília, Advogado
2, José, Anápolis, Engenheiro
3, João Carlos, Goiânia, Advogado Trabalhista
4, João da Silva, Goiânia, Médico

The result for search 1 and 2 would be records 3 and 4, and for search 3 would be just record 3.

I thought about using campo IN (arg1, arg2, arg3) , but there is a possibility that the argument is not complete, as in the example. What is the best solution?

My database is: PostgreSQL 9.4

sql postgresql

asked by anonymous 31.08.2018 / 21:29

1 answer

Shortcode wordpress Help in VB.NET using Newtonsoft.Json

score 1 · Answer 1

If it's SQL SERVER, it can be done like this:

SELECT * FROM NOME_DA_TABELA WHERE LTRIM(RTRIM(NOME))+LTRIM(RTRIM(CIDADE))+LTRIM(RTRIM(PROFISSÃO)) LIKE '%JoãoGoiânia%'

SELECT * FROM NOME_DA_TABELA WHERE LTRIM(RTRIM(NOME))+LTRIM(RTRIM(CIDADE))+LTRIM(RTRIM(PROFISSÃO)) LIKE '%JoãoGoiâniaAdvogado%'

Concatenate the columns, take the spaces and use the like with two%, one at the beginning and one at the end. AI just search with the filters you want, but without any space and in the order you ordered in the WHERE clause