Ignore dot in a Query

Asked

Viewed 215 times

0

I am trying to run the following Query:

select codigo from cliente where codigo ilike '%99.999.999%';

I thought by putting the ilike, he would ignore everything, points and accents but I was wrong.

In my system, I have a query screen where I prefer that the user does not need to type points... When he searches a CPF for example

Detail: I use this query in a Textchanged of a Textbox

How could I do this query to ignore the dots?

c# sql postgresql

  • mount the query without the dots..

  • On my system, I have a query screen where I prefer that the user does not need to type points... When he searches a CPF for example

  • Remove the dot when passing text from the screen to the query?

  • I’m doing this query in a field in the event Textchanged. At the time he type, already go making the query

  • 1

    if this value is a user input, just remove the points before playing it in the query. You can do this in C# itself or SQL.. The fact that the data comes from a "Textchanged" event makes no difference..

2 answers

2

Avoid injecting application complexity into sql querys. This makes maintenance difficult, because reusing SQL (strings) is practically 0.

The ideal serial construction of a Cpf object:

public class Cpf{

     private string _valorSemMascara;

     public string CpfComMascara {
         get{
              // retorna _valorSemMascara incluindo máscara por concatenação
          }
          set{
             // remove máscara de 'value' e atribui valor em _valorSemMascara
          }

     }

     public string CpfSemMascara {

          get{
             return _valorSemMascara;
          }
          set{
             _valorSemMascara = value;
          }
     }

}

This object would be reused in several situations within your system.

You can use it from your 'text_changed' to the point of inclusions, changes and obtainments (selects).

Only in the situation you described and in a CRUD, how many times would you repeat the 'REPLACE' method in the query? And if you decide to change DBMS? Will leave your query stuck to Postgresql?

  • Thank you!!!! ^^

1


You can use the function REPLACE to remove the points. Below is an example.

REPLACE('99.999.999', '.','')

  • Thanks, it worked!

Browser other questions tagged c# sql postgresql

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