problem with accents
A user on IDS 10.00.FC4 found that LOWER() left accented characters untouched ("GARCÍA" became "garcÍA"-style mixed case), and asked whether a database or JDBC setting could fix it. Art Kagel suggested opening a support case; others asked about locale settings and noted it worked for them in dbaccess. Gerald Cousins explained it is not a bug: with the default locale (e.g. en_us.8859-1) IDS only does upper/lower case conversion for ASCII characters below 0x7f. The poster worked around it with a custom SPL function using LOWER plus REPLACE calls to strip accents before LIKE comparisons.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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?
If you are using the locale "en_us.8859-1" then it is not a bug. IDS documents that the default locale only supports upper and lower case translation for ASCII (ie codepoints with a value < 0x7f) "scottishpoet" <dryburghj@yahoo.com> Sent by: informix-list-bounces@iiug.org 03/21/2007 10:24 AM To informix-list@iiug.org cc Subject Re: problem with accents 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? _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list