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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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":
I have created a table, and, input a few rows, as below:
NOTE: In the above, three rows for COUNTRY include ua as text.
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