Hi All,

Given a varchar(50) field...is there a way to query in a "accent/diacritics" insensitive way?

For example, having the name "Urrutia" I want to get the record containing the name " Urrútia" which has an accent on the "u".

Martin Bowes

Somenthing a bit grander than...
select doctor_id, surname, hex(surname)
from staff
where surname like 'Urr' + X'FA' + '%'

From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 24 January 2011 15:53
To: Ingres and related product discussion forum
Subject: [Info-Ingres] FW: Accent/diacritics insensitivity?

Hi All,

Given a varchar(50) field...is there a way to query in a "accent/diacritics" insensitive way?

For example, having the name "Urrutia" I want to get the record containing the name " Urrútia" which has an accent on the "u".

Martin Bowes

Hi Marty,

The short answer is no. The pattern engine used in like & similar to was setup so that Unicode types could one day have a 'WITHOUT DIACRITICAL' qualifier but that needs a little code adding to access it. A good sprint project!

Regards,

Ian



________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 24 January 2011 16:07
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] FW: Accent/diacritics insensitivity?



Somenthing a bit grander than...

select doctor_id, surname, hex(surname)

from staff

where surname like 'Urr' + X'FA' + '%'



From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 24 January 2011 15:53
To: Ingres and related product discussion forum
Subject: [Info-Ingres] FW: Accent/diacritics insensitivity?



Hi All,



Given a varchar(50) field...is there a way to query in a "accent/diacritics" insensitive way?



For example, having the name "Urrutia" I want to get the record containing the name " Urrútia" which has an accent on the "u".



Martin Bowes

Hi Marty,



If I undestood well, I know a way to do this with use of collation sequence. But it is necessary that database has been created with specific (customized?) collation sequence file.

May be there are other ways to do this. I heard sometime ago Ingres would have the possibility to define collation sequence by collumn.

System Admin Guide has some notes about this (Page 93 - Ingres 2006 R2 docs).

BTW, this way may be so drastic, but it works well here with a customized collation file I did.



Best regards,



Leandro Fava

Setor de Informática - UNISC

+55 51 3717 7636



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: segunda-feira, 24 de janeiro de 2011 13:53
To: Ingres and related product discussion forum
Subject: [Info-Ingres] FW: Accent/diacritics insensitivity?



Hi All,



Given a varchar(50) field...is there a way to query in a "accent/diacritics" insensitive way?



For example, having the name "Urrutia" I want to get the record containing the name " Urrútia" which has an accent on the "u".



Martin Bowes

On 24/01/2011 15:52, Martin Bowes wrote:

I have an extra column called searchable_text and use the following
mapping table to convert 'strange' characters to ascii equivalent(s).
The conversion is done by C code.

This is a hex mapping. the first value is the extended ascii value and
the second (and maybe third, forth and fifth) are the translated value.

0x00A1,0x0021
0x00A9,0x0028,0x0063,0x0029
0x00AA,0x0061
0x00B2,0x0032
0x00B3,0x0033
0x00B9,0x0031
0x00BA,0x006F
0x00BC,0x0031,0x002F,0x0034
0x00BD,0x0031,0x002F,0x0032
0x00BE,0x0033,0x002F,0x0034
0x00BF,0x003F
0x00C0,0x0041
0x00C1,0x0041
0x00C2,0x0041
0x00C3,0x0041
0x00C4,0x0041
0x00C5,0x0041
0x00C6,0x0041,0x0065
0x00C7,0x0043
0x00C8,0x0045
0x00C9,0x0045
0x00CA,0x0045
0x00CB,0x0045
0x00CC,0x0049
0x00CD,0x0049
0x00CE,0x0049
0x00CF,0x0049
0x00D0,0x0054,0x0068
0x00D1,0x004E
0x00D2,0x004F
0x00D3,0x004F
0x00D4,0x004F
0x00D5,0x004F
0x00D6,0x004F
0x00D8,0x004F,0x0065
0x00D9,0x0055
0x00DA,0x0055
0x00DB,0x0055
0x00DC,0x0055
0x00DD,0x0059
0x00DE,0x0054,0x0068
0x00DF,0x0073,0x0073
0x00E0,0x0061
0x00E1,0x0061
0x00E2,0x0061
0x00E3,0x0061
0x00E4,0x0061
0x00E5,0x0061
0x00E6,0x0061,0x0065
0x00E7,0x0063
0x00E8,0x0065
0x00E9,0x0065
0x00EA,0x0065
0x00EB,0x0065
0x00EC,0x0069
0x00ED,0x0069
0x00EE,0x0069
0x00EF,0x0069
0x00F0,0x0074,0x0068
0x00F1,0x006E
0x00F2,0x006F
0x00F3,0x006F
0x00F4,0x006F
0x00F5,0x006F
0x00F6,0x006F
0x00F8,0x006F,0x0065
0x00F9,0x0075
0x00FA,0x0075
0x00FB,0x0075
0x00FC,0x0075
0x00FD,0x0079
0x00FE,0x0069,0x006A
0x00FF,0x0079

On 25/01/11 08:21, nikosv_ wrote:
NFC with the createdb "-i" flag. See http://bit.ly/Ingres92createdb for
more info.

g

--
Grant Croker, Ingres Corp
Ingres PHP and Ruby maintainer
http://planetingres.org
It was so much easier to blame it on Them. It was bleakly depressing to think that They were Us. If it was Them, then nothing was anyone's fault. If it was us, what did that make Me? After all, I'm one of Us. I must be. I've certainly never thought of myself as one of Them. No one ever thinks of themselves as one of Them. We're always one of Us. It's Them that do the bad things.
-- Terry Pratchett, Jingo