Re: problem with query

[Chris Curvey <chris@xxxxxxxxxxxxxxx>]

On Thu, Sep 12, 2013 at 5:33 PM, Roberto Scattini <roberto.scattini@xxxxxxxxx> wrote:

On Thu, Sep 12, 2013 at 6:22 PM, Chris Curvey <chris@xxxxxxxxxxxxxxx> wrote:

On Thu, Sep 12, 2013 at 4:34 PM, Roberto Scattini <roberto.scattini@xxxxxxxxx> wrote:

we are using postgresql 9.1 from ubuntu packages and the database encoding is (sadly) SQL_ASCII

can anybody point me in the right direction to solve this issue?

--
Roberto Scattini

If you could tells what you are expecting, and what you are actually getting, that would be helpful.

hi chris, sorry, the query with ilike '%NUÑEZ%'  is returning 4 rows and the query with ilike '%nuñez%' is returning 0 rows.

a simplified version of the query with his results:

pgn=# set client_encoding = SQL_ASCII;

SET

pgon=# SELECT DISTINCT

 p.id, p.apellido, p.nombre

 FROM personal.personas AS p

  WHERE p.apellido ilike '%nuñez%';

 id | apellido | nombre

----+----------+--------

(0 rows)

pgon=# SELECT DISTINCT

 p.id, p.apellido, p.nombre

 FROM personal.personas AS p

  WHERE p.apellido ilike '%NUÑEZ%';

  id   |    apellido    |     nombre

-------+----------------+-----------------

 39489 | NUÑEZ          | JUAN

 39937 | PEREZ NUÑEZ    | FRANCISCO

 39498 | NUÑEZ ESQUIBEL | RICARDO RODOLFO

 40220 | NUÑEZ VERA     | MANUEL SANTIAGO

(4 rows)

Is this just a case-sentitvity issue?  if personas.apellido is a varchar field, then I think that's your trouble.  (it would have to be citext in order for "nunez" = "NUNEZ".)