Hi,

I have a table with an [lastname varchar(255) not null] attribute that
stores strings with accents.

if I execute the following query:

select lower(lastname) from mytable

when the lastname data is "GARCÍA", it returns "garcÍa" (note that the
I with accent is NOT lowercased). The correct result should be
"garcía" (note the í lowercased) or in any case "garcia" (without
accents), but never a mixed lowercase and uppercase string.

Is there any database setting or JDBC driver setting that I can to
touch to get the desired result?

My configuration:
Informix Dynamic Server 10.00.FC4XD
IBM Informix JDBC Driver for IBM Informix Dynamic Server 3.00.JC3

TIA,
Gabriel

Gabriel Belingueres wrote:
> Hi,
>
> I have a table with an [lastname varchar(255) not null] attribute that
> stores strings with accents.
>
> if I execute the following query:
>
> select lower(lastname) from mytable
>
> when the lastname data is "GARCÍA", it returns "garcÍa" (note that the
> I with accent is NOT lowercased). The correct result should be
> "garcía" (note the í lowercased) or in any case "garcia" (without
> accents), but never a mixed lowercase and uppercase string.
>
> Is there any database setting or JDBC driver setting that I can to
> touch to get the desired result?
>
> My configuration:
> Informix Dynamic Server 10.00.FC4XD
> IBM Informix JDBC Driver for IBM Informix Dynamic Server 3.00.JC3

I'd say this is a good one to open a support case for.

Art S. Kagel

On 20 mar, 17:25, "Art S. Kagel" <k...@bloomberg.net> wrote:
> Gabriel Belingueres wrote:
> > Hi,
>
> > I have a table with an [lastname varchar(255) not null] attribute that
> > stores strings with accents.
>
> > if I execute the following query:
>
> > select lower(lastname) from mytable
>
> > when the lastname data is "GARCÍA", it returns "garcÍa" (note that the
> > I with accent is NOT lowercased). The correct result should be
> > "garcía" (note the í lowercased) or in any case "garcia" (without
> > accents), but never a mixed lowercase and uppercase string.
>
> > Is there any database setting or JDBC driver setting that I can to
> > touch to get the desired result?
>
> > My configuration:
> > Informix Dynamic Server 10.00.FC4XD
> > IBM Informix JDBC Driver for IBM Informix Dynamic Server 3.00.JC3
>
> I'd say this is a good one to open a support case for.
>
> Art S. Kagel- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

Is this a bug? I was thinking the database had some charset or
collation property misconfigured!

Anyway, I worked around the problem by writing a function that returns
me the right string for performing comparisons:

create function tolowercase(s varchar(255)) returning varchar(255);
define m varchar(255);

if s is null then
return null;
end if;

let m = lower(s);
let m = replace(m, 'á', 'a');
let m = replace(m, 'Á', 'a');
let m = replace(m, 'é', 'e');
let m = replace(m, 'É', 'e');
let m = replace(m, 'í', 'i');
let m = replace(m, 'Í', 'i');
let m = replace(m, 'ó', 'o');
let m = replace(m, 'Ó', 'o');
let m = replace(m, 'ú', 'u');
let m = replace(m, 'Ú', 'u');

return m;
end function

I originally wanted to build an sql query that search for some %string
% with the _like_ keywork.
The above function solves my problem because I compare the database
data againts unaccented words: select .... where tolowercase(lastname)
like '%cia%'.
The only condition is that I have to remove accents too from the query
parameters...but doing this with java is straightforward.

Thanks Art. Please someone open a case for me.

Regards,
Gabriel

This seems to work for german umlauts and accented characters using dbaccess (no locale settings):

andreasl@vmlinux:~> echo 'select s, c, lower(c), upper(c) from tb where s >= 58'|db7 2>/dev/null


s c (expression) (expression)

58 ÄÖÜ äöü ÄÖÜ
59 äöü äöü ÄÖÜ
60 àaÀA àaàa ÀAÀA
61 ÍIíi íiíi ÍIÍI

But: in my Linux bash, the ~ command seems to have a similar problem with umlauts, and with accented characters as well ;-)

May I ask what language settings you are using for your database and client?

Andreas

Gabriel Belingueres wrote:
> Hi,
>
> I have a table with an [lastname varchar(255) not null] attribute that
> stores strings with accents.
>
> if I execute the following query:
>
> select lower(lastname) from mytable
>
> when the lastname data is "GARCÍA", it returns "garcÍa" (note that the
> I with accent is NOT lowercased). The correct result should be
> "garcía" (note the í lowercased) or in any case "garcia" (without
> accents), but never a mixed lowercase and uppercase string.
>
> Is there any database setting or JDBC driver setting that I can to
> touch to get the desired result?
>
> My configuration:
> Informix Dynamic Server 10.00.FC4XD
> IBM Informix JDBC Driver for IBM Informix Dynamic Server 3.00.JC3
>
> TIA,
> Gabriel
>

Gabriele

It might be a bug or it might be something to do with the locale you
are using

What are your various locale settings?