I'm unable to write a working LIKE query for a field containing the German sharp-s (ß) in a case-insensitive text field.
Using HSQLDB 2.2.9, create a table with a case sensitive field and a case insensitive field.
CREATE CACHED TABLE MYTABLE (MYKEY LONGVARCHAR NOT NULL, PRIMARY KEY (MYKEY));
ALTER TABLE MYTABLE ADD COLUMN SEN LONGVARCHAR;
ALTER TABLE MYTABLE ADD COLUMN INSEN VARCHAR_IGNORECASE;
Write 2 records.
INSERT INTO MYTABLE (MYKEY, SEN, INSEN) VALUES ('1', 'Strauß', 'Strauß');
INSERT INTO MYTABLE (MYKEY, SEN, INSEN) VALUES ('2', 'Strauss', 'Strauss');
Verify.
SELECT * FROM MYTABLE
KEY, SEN, INSEN
'1', 'Strauß', 'Strauß'
'2', 'Strauss', 'Strauss'
The problem query:
SELECT * FROM MYTABLE WHERE INSEN LIKE '%ß%'
WRONG, RETURNS RECORD 2 NOT RECORD 1
These queries work as expected:
SELECT * FROM MYTABLE WHERE SEN LIKE '%ß%'
OK, RETURNS RECORD 1
SELECT * FROM MYTABLE WHERE UCASE(INSEN) LIKE '%ß%'
OK, RETURNS RECORDS 1 AND 2
SELECT * FROM MYTABLE WHERE UCASE(SEN) LIKE '%ß%'
OK, RETURNS NOTHING
SELECT * FROM MYTABLE WHERE SEN='Strauß'
OK, RETURNS RECORD 1
SELECT * FROM MYTABLE WHERE INSEN='Strauß'
OK, RETURNS RECORD 1
SELECT * FROM MYTABLE WHERE SEN='Strauss'
OK, RETURNS RECORD 2
SELECT * FROM MYTABLE WHERE INSEN='Strauss'
OK, RETURNS RECORD 2
Thanks!