SAP IQ Accent Insensitive

[Former Member]

Hi experts,

I searched a while before posting here... I'm facing an issue with SAP IQ 16.

All I try to do is to be able of search a letter or a word within a varchar type, no matter the case and the accents.

For the case, it's ok : I created the database "CASE IGNORE". This way, when I look for the word "one" in a specific column of a specific table, I am able to find the words : one, oNe, ONE... and so on.

But, despite my efforts, I cannot do the same with the accents.

Let's say I got a table "LABEL" with a column "COL_LABEL". There is one row : "aéa"

If I try the SQL statement : select * from LABEL where COL_LABEL like '%aea%', it doesn't work.

So my question is quite easy : is there a way to look for a word despite the accent?

PS : I don't want to use the UPPER function 😃

Kind regards,

[Gisung · Accepted solution]

Hi,

The following SQL displays the result of "accent sensitive" in IQ. 

> select db_property('AccentSensitive');

  db_property('AccentSensitive')

  'Off'

In my opinion,

when creating database with "ACCENT IGNORE", IQ ignores the accent.

CREATE DATABASE '/DEV/test.db'

LOG ON '/DEV/test.log'

CASE RESPECT

ACCENT IGNORE

PAGE SIZE 4096

COLLATION 'ISO_BINENG'

BLANK PADDING ON

...

==

Gi-Sung Jang

[Former Member · Accepted solution]

Hi,

Just like you said, my value of AccentSensitive was On before. So I re-created the database with ACCENT IGNORE option :

select db_property('AccentSensitive'); -- Off

But it still doesn't work :

create table libelle (col varchar(15))

insert into libelle values ('eé');

insert into libelle values ('aA');

select * from libelle where col like '%ee%' -- 0 row

select * from libelle where col like '%aa%' -- 1 row

Regards,

[Former Member · Accepted solution]

It's OK : It works if I choose "ISO_1" as collation at the creation of the database 😃

[tayeb_hadjou · Accepted solution]

Hi,

You may perefer the extension iso9 (COLLATION 'ISO9LATIN1' ) wich includes Euro symbol.

Restrictions for iso1 or iso9 :

1- Note that accents are also ignored when sorting.

2- You cannot import and store properly international data. http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc01772.1603/doc/html/san1288042961627...

Regards,