SQL Query with multiple arguments

Asked

Viewed 430 times

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 must go through three fields: name, city, profession. Since the arguments can be in one, two or in 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 only record 3.

I thought I’d use campo IN (arg1, arg2, arg3), however there is the possibility that the argument is not complete, as in the example. What is the best solution?

My database is Postgresql 9.4

sql postgresql

  • What is your database? Mysql, SQL Server, Firebird....

  • since you don’t know how many fields or the order, one option would be to create a *full text index'

  • @Matheusribeiro Postgresql 9.4

  • @Ricardopunctual I’m reading about your suggestion, and actually the textual search seems to me to be a good solution. As soon as you find a good and complete article on the subject put here.

  • Siml to the postgresql I believe it is. Here’s a good read: full text

1 answer

1

If it is 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%'

I concatenate the columns, remove 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

  • Good answer, but instead of using LTRIM(RTRIM(ID)) it wouldn’t be interesting to just use TRIM(ID)? This command already takes the spaces from the end and start of each string, so it would be cleaner the script :D

  • I thought of using the trim, but here was giving this error: http://prntscr.com/kp7wgb That’s why I used the ltrim(rtrim(' '))

  • I understand, it’s probably the version of your bank It works right here

  • The problem I see of concatenating is when one of the arguments is not complete, as in my example, the table name is João Carlos and the user informed only João. Still has the profession and the city that can happen the same thing.

  • It’s... that’s a real problem... let me see if I can think of something

  • If it were sql-server, would be better to use the CONTAINS...

Show 1 more comment

Browser other questions tagged sql postgresql

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