Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

COLLATION "SQL_TEXT_UCC" an...

Help
2013-06-30
2014-01-19
  • I am using HSQL 2.3.0 Beta, version hsqldb-20130629.235800-51.jar, the latest Snapshot as of this date.

    I am finding some search ( Select ) statement irregularities that I cannot explain, and, I think it may be new / different with the latest snapshot version.

    My database, includes, in the SCRIPT file, the following statement ( as described in the documentation, Chapter 4 - Schemas and Database Objects - Collations ), and, I have issued the command, SHUTDOWN COMPACT, to ensure indices etc are re-created.:

    SET DATABASE COLLATION "SQL_TEXT_UCC"

    With the above command, my understanding is that any 'search' should be CASE ( UPPER / lower ) insensitive. The field I am searching on, is defined as VARCHAR(9), and, the content is all UPPER CASE.

    The following statement works exactly as expected, returning one record:

    Select GRPTIME.* From GRPTIME Where GRPTIME.DESCRIPT = 'lunCH';
    

    But, the following finds ZERO records, despite the fact it should have returned ONE record ( but does NOT match CASE ):

    Select GRPTIME.* From GRPTIME Where GRPTIME.DESCRIPT LIKE '%nC%';
    

    Likewise, the use of the BUILT-IN functions, LOCATE and INSTR . . . where I thought it find records, regardless of CASE ( UPPER / lower ) apparently will only return records if it ALSO matches case, despite having set,  COLLATION "SQL_TEXT_UCC" .

    Sliderule

     
  • Fred Toussi
    Fred Toussi
    2013-07-04

    Thanks.

    The LOCATE and INSTR functions always use the SQL_TEXT collation and are case-sensitive.

    LIKE allowed case-insensitive comparison in previous versions. The next snapshot will restore the bahaviour.

     
  • Fred:

    Thank-you for responding.

    Regarding use of functions, similar ( like ) to LOCATE and INSTR always using SQL_TEXT collation, I have no problem with this.

    My only suggestion is, someplace in the documentation, perhaps this could be mentioned.

    Thanks.

    Sliderule