Informix

 View Only
  • 1.  unaccent where clause ...

    Posted Mon March 07, 2022 01:07 PM
    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
    ------------------------------


  • 2.  RE: unaccent where clause ...

    Posted Mon March 07, 2022 01:13 PM

    Have you looked at the Regex datablade ?

     






  • 3.  RE: unaccent where clause ...

    Posted Mon March 07, 2022 01:40 PM
    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
    ------------------------------



  • 4.  RE: unaccent where clause ...

    IBM Champion
    Posted Tue March 08, 2022 02:52 AM

    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
    ------------------------------



  • 5.  RE: unaccent where clause ...

    Posted Tue March 08, 2022 07:01 AM
    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.





  • 6.  RE: unaccent where clause ...

    Posted Tue March 08, 2022 12:46 PM
    Hi,

    NCHAR is only for case sensitive if i'm not wrong ?

    Regards

    ------------------------------
    Garcia Benjamin
    ------------------------------



  • 7.  RE: unaccent where clause ...

    IBM Champion
    Posted Tue March 08, 2022 01:39 PM
    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
    ------------------------------



  • 8.  RE: unaccent where clause ...

    Posted Tue March 08, 2022 01:56 PM
    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





  • 9.  RE: unaccent where clause ...

    IBM Champion
    Posted Tue March 08, 2022 01:59 PM
    Hal:

    Thanks for the correction.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------