NCHAR and NVARCHAR are only case insensitive in a case insensitive database, as are all character types. Witness, in my "normal" database:
> create table with_nchar( one serial, two nchar(30));
Table created.
> insert into with_nchar values (0,'john' );
1 row(s) inserted.
> insert into with_nchar values (0,'JOHN');
1 row(s) inserted.
> select * from with_nchar where two = 'john';
one two
1 john
1 row(s) retrieved.
> select * from with_nchar where two = 'JOHN';
one two
2 JOHN
1 row(s) retrieved.
The only thing that NCHAR and NVARCHAR bring to the table in any database - versus CHAR, VARCHAR, and LVARCHAR - is the option to declare your own collation order for handling non-English and custom sorting.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Tue March 08, 2022 12:45 PM
From: Garcia Benjamin
Subject: unaccent where clause ...
Hi,
NCHAR is only for case sensitive if i'm not wrong ?
Regards
------------------------------
Garcia Benjamin
Original Message:
Sent: Tue March 08, 2022 07:00 AM
From: Hal Maner
Subject: unaccent where clause ...
Another option is to use the case insensitive database property along with NCHAR columns for the fields you need this with. Uses regular dbspaces, regular indexes. We have been using this with very good results in some new solutions we developed in the past 6-7 years. We were able to design them this way from the ground up. To add to an existing database/solution would take some work and downtime (other options likely would also) but we have been very happy with the way this works from all viewpoints.
Hal Maner
M Systems International, Inc.
Original Message:
Sent: 3/8/2022 2:52:00 AM
From: Henri Cujass
Subject: RE: unaccent where clause ...
Hi Garcia,
if you don't like to use the BTS functionality the old default solution is to add a varchar or char column and fill it with your own transformation rules string. This can be a combination of more attributes, too. (e.g. pseudocode of the first+last name+location). The expression string for the like must transform in the same way. It's a kind of "cast" operator.
But I suggest using BTS ;-)
Best Regards
------------------------------
Henri Cujass
leolo IT, CTO
Germany
IBM Champion 2021
Original Message:
Sent: Mon March 07, 2022 01:07 PM
From: Garcia Benjamin
Subject: unaccent where clause ...
Hello,
i'm looking for a nice solution to make search "non case sensitive" and/or "non accent sensitive".
Sample : name of user "éric" i'll find it in a where clause user like '%ERIC%' ...
i know that we can create a "search column" parsed with a cleaned value but it's a global solution
Thanks in advance
------------------------------
Garcia Benjamin
------------------------------
#Informix