How to make a select with multiple `Where field like '%'` in C#

Asked

Viewed 42,978 times

2

select below even works, but it searches all fields and would like the idaluno, nomealuno and cpf were searched and ordered by idaluno

SELECT idaluno,nomealuno,responsavel,cpf,rg,fone_contato,desistente 
    FROM aluno 
    WHERE idaluno LIKE '%12%' OR nomealuno LIKE '%%'  OR cpf LIKE '%%' 
    ORDER BY idaluno;
c# sql

  • The correct tag would not be [tag:sql]?

3 answers

2

The SQL that is used in C# has some particularities, for you to make a query using LIKE, you should be aware of some things..

  • If the value won’t be null or empty "", Empty

Be aware also in NEVER CONCATENATE, query strings. That is @Angelo’s answer, you may suffer from sql injection attacks. Whatever this is conversation to another question...

My answer to your problem..

using(MySqlCommand cmd = new MySqlCommand(@"SELECT passe_os_campos_relevantes_para_voce_aqui FROM aluno WHERE ((idaluno = @id) OR (nomealuno LIKE '%' @nomealuno '%') OR (cpf = @cpf))"), new MySqlConnection("passe_sua_string_de_conexao")){
    cmd.Parameters.AddWithValue("@id", id);
    cmd.Parameters.AddWithValue("@nomealuno", nome);
    cmd.Parameters.AddWithValue("@cpf", cpf);
}

I recommend never comparing with LIKE, primary key values, there is no why, nor with the CPF field, as the values will NEVER be approximated either they are or they are not!! Create a connection class. I don’t know if you did, but since your question is simple, it’s worth a few more tips! ;D

Valeuu!

1

Try it this way

Select idaluno, nomealuno,responsal,cpf,rg,fone_contato,desistente
from aluno
where idaluno = (like '%"+ @idaluno +"%')
OR
(nomealuno = like'%"+ @nomealuno +"%')
OR
(CPF='"+ @cpf_aluno+"');

remember to put this script in a string that can be executed then passing the data by parameter! (Addparameter, unless I’m mistaken it’s something like this - as soon as I get home I lap here an example of my application).

I recommend you to search only by CPF, or only by RG. In this context, the bank will be able to return several results and it will not be easy for the user to find what you really need.

A search by Name or CPF already kills much of this issue.

I hope I helped you!

-2

select * from TABELA1 T1 where CAMPO.T1 in (select T11.CAMPO from TABELA1 T11 
inner join TABELA2 T2 on  T11.CAMPO like '%'||T2.CAMPO||'%')
  • 3

    Could explain your solution better?

  • I had to delete records in Table 1 that the search would be for the field1, this field1 had an equivalent in table 2. Since the field1 in Table 1 had some more details than it had in table 2. Therefore, I made a select searching for field 1 in table 1 with another alias (T11) and an Inner Join with table 2 using the like to come the records even with the minimum difference. Hence only use in () to search in Table 1 with the alias T1. In ORACLE and Firebird || is to concatenate.

  • 1

    And how would your answer serve the author of the question (assuming that four years later he is still interested in it)? How would you edit the SQL that is in the question statement based on the idea you give in your answer?

  • delete from email and Where e.email IN (select e.email from emailerrado er Where e.email like '%'||ER.Emailer|'%' ) This worked better!!! with respect to if it would serve.

  • Delete!? I don’t see anything in the question suggesting that the author is interested in deleting something from their database.

Browser other questions tagged c# sql

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