Sliderule Smith - 2014-07-05

I am using HSQL Version 2.3.2.

For my database, I issue the command below, so, "collation is a case-insensitive form of the UNICODE_SIMPLE collation":

SET DATABASE COLLATION "SQL_TEXT_UCC";

I have created a table, and, input a few rows, as below:

-- Create a sample demonstration table
CREATE TABLE WORLD_POPULATION (COUNTRY VARCHAR(40) NOT NULL PRIMARY KEY, POPULATION BIGINT NOT NULL);

-- Insert sample data. NOTE: three occurrences of text string 'ua' in COUNTRY below
INSERT INTO WORLD_POPULATION VALUES ('Argentina', 40134425);
INSERT INTO WORLD_POPULATION VALUES ('Boliva', 9879000);
INSERT INTO WORLD_POPULATION VALUES ('Brasil', 192714000);
INSERT INTO WORLD_POPULATION VALUES ('Chile', 17053000);
INSERT INTO WORLD_POPULATION VALUES ('Colombia', 45377000);
INSERT INTO WORLD_POPULATION VALUES ('Ecuador', 14155000);
INSERT INTO WORLD_POPULATION VALUES ('Falkland Islands (Islas Malvinas)', 3000);
INSERT INTO WORLD_POPULATION VALUES ('Guyana', 762000);
INSERT INTO WORLD_POPULATION VALUES ('Paraguay', 6349000);
INSERT INTO WORLD_POPULATION VALUES ('Peru', 29132013);
INSERT INTO WORLD_POPULATION VALUES ('Suriname', 520000);
INSERT INTO WORLD_POPULATION VALUES ('Uruguay', 3361000);
INSERT INTO WORLD_POPULATION VALUES ('Venezuela', 28723000);

NOTE: In the above, three rows for COUNTRY include ua as text.

-- Below returns three rows exactly as expected, using || to concatenate string to tbe searched
Select * From WORLD_POPULATION Where COUNTRY || SPACE(1) || 'South America' LIKE '%uA%';

-- Below returns three rows exactly as expected, using CONCAT to concatenate string to tbe searched
Select * From WORLD_POPULATION Where CONCAT(COUNTRY, SPACE(1), 'South America') LIKE '%uA%';

-- Below returns ZERO rows BUT should return 3, using CONCAT_WS to concatenate string to tbe searched
Select * From WORLD_POPULATION Where CONCAT_WS(SPACE(1), COUNTRY, 'South America') LIKE '%uA%';

-- Below returns three rows exactly as expected, using CONCAT_WS to concatenate string to tbe searched
-- NOTE: below the search string DOES match case '%ua'
Select * From WORLD_POPULATION Where CONCAT_WS(SPACE(1), COUNTRY, 'South America') LIKE '%ua%';

Explanation: When searching using a LIKE statement, calculation to the left of LIKE in the WHERE clause works as expected, including matching case when using either, || or CONCAT, however, CONCACT_WS does not 'respect' the case the same way.

Sliderule