1

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!

peterm
  • 91,357
  • 15
  • 148
  • 157
  • Actually the "problem query" should return *bot* rows, not only one. –  Jul 22 '13 at 06:23

0 Answers0