unaccent where clause ...
Garcia Benjamin wanted case- and accent-insensitive search in a WHERE clause. Paul Watson suggested the Regex datablade; Benjamin disliked BTS. Henri Cujass suggested a helper column with normalised text. Hal Maner proposed a case-insensitive database with NCHAR columns and showed CHAR stays case-sensitive; Art Kagel accepted the correction.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
Have you looked at the Regex datablade ?
Hi, Yes but with regex we must construct/change our WHERE clauses with bts_contains and this is not standard ... Same with "basic text search" blade ------------------------------ Garcia Benjamin ------------------------------
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 ------------------------------
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.
Hi, NCHAR is only for case sensitive if i'm not wrong ? Regards ------------------------------ Garcia Benjamin ------------------------------
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
------------------------------
Art, "CHAR" data type does not behave case insensitive in my case insensitive database. That is why we had to create NCHAR columns. I tested this with your example but with "two" as a CHAR column. Only exact case match returns in the selects. I then tested your example with a NCHAR column. No matter what WHERE clause I use ('john' or 'JOHN') both rows are returned. This is a case insensitive en_US.utf8 database. @Garcia: please do a Google search for "NCHAR informix" and read the manual - quite good explanations there. Hal
Hal: Thanks for the correction. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------