How to do a Query ignoring the side spaces of the Column content?

Question

Good morning, everyone!

Using the Query:

@Repository public interface SessaoRepository extends AbstractRepository<Sessao, Long> { @Query(value = "SELECT p FROM Sessao p WHERE p.dc_secao =:dc_secao") Sessao getDcSessao(@Param("dc_secao") String dc_secao); }

I have a return that is a number, the problem is that within the table Sessao the data within dc_secao may be in these two cases:

     dc_secao           | cod_secao
  SECAO DO FUEGO          123810230
  /t/tSECAO DE GELO\t\t   3123131313

I mean, in case I look SECAO DE GELO I won’t return anything, there is some Query where I can ignore these spaces (represented by \t) to bring the results independent of the lateral spaces?

Answer 1

Matheus,

You can use the function trim in the field you want to filter inside your Where, something like this:

SELECT * FROM Sessao p WHERE trim(p.dc_secao) = 'SECAO DE GELO';

If you have tabs, the trim may not work, for that there is the btrim, that you can send a char that will be replaced, then you send the char referring to tab, in Postgres you can use the chr(9):

SELECT * FROM Sessao p WHERE btrim(p.dc_secao, chr(9)) = 'SECAO DE GELO';

It is possible to mix the functions, call one function with the return of another:

SELECT * FROM Sessao p WHERE trim(btrim(p.dc_secao, chr(9))) = 'SECAO DE GELO';

It is also possible to use the operator like SQL, where % represents a joker, with this you can ignore the beginning and the end of the researched term, this form can represent a risk to the research, since it looks for a contained, you can return some data improper, but here is the idea:

SELECT * FROM Sessao p WHERE p.dc_secao like '%SECAO DE GELO%';

See online: http://sqlfiddle.com/#! 17/2fdd5/3

Reference: https://www.postgresql.org/docs/9.1/functions-string.html