SCRIPT
BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE "LikeTest"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"ADESCR" VARCHAR(50) COLLATE "ANSI",
"ADESCR2" VARCHAR(50) COLLATE "ANSI" GENERATED ALWAYS AS ADESCR

)

EXECUTE IMMEDIATE 'INSERT INTO "LikeTest" VALUES (1,
                  ''///WOOL 100%'',
                  ''///WOOL 100%'')';

EXECUTE IMMEDIATE 'INSERT INTO "LikeTest" VALUES (2,
                  ''//COTTON 50%/WOOL 50%'',
                  ''//COTTON 50%/WOOL 50%'')';

EXECUTE IMMEDIATE 'INSERT INTO "LikeTest" VALUES (3,
                  ''/LICRA 20%/COTTON 30%/WOOL 50%'',
                  ''/LICRA 20%/COTTON 30%/WOOL 50%'')';

EXECUTE IMMEDIATE 'INSERT INTO "LikeTest" VALUES (4,
                  ''//COTTON 50%/WOOL 50%'',
                  ''//COTTON 50%/WOOL 50%'')';

EXECUTE IMMEDIATE 'CREATE INDEX "LikeTest_ix1" ON "LikeTest" ("ADESCR" COLLATE "ANSI")';

END

--ADESCR IS PHYSICAL INDEXED COLUMN
--QUERY DOES NOT RETURN RESULTS
SELECT * FROM LIKETEST WHERE ADESCR LIKE '%30/%%' ESCAPE '/'

--ADESCR2 IS GENERATED NON INDEXED COLUMN
--QUERY WORKS FINE
SELECT * FROM LIKETEST WHERE ADESCR2 LIKE '%30/%%' ESCAPE '/'

--ESCAPE ESCAPE CHARACTER
--PASS
SELECT * FROM LIKETEST WHERE ADESCR2 LIKE '//////WOOL%' ESCAPE '/'
--FAIL
SELECT * FROM LIKETEST WHERE ADESCR LIKE '//////WOOL%' ESCAPE '/'
--MUST NOT ESCAPE ESCAPE CHAR TO WORK
SELECT * FROM LIKETEST WHERE ADESCR LIKE '///WOOL%' ESCAPE '/'
--PASS
SELECT * FROM LIKETEST WHERE ADESCR2 LIKE '//////%' ESCAPE '/'
--MUST ESCAPE ESCAPE CHAR TO WORK
--PASS
SELECT * FROM LIKETEST WHERE ADESCR LIKE '//////%' ESCAPE '/'