Subject Case insensitive SIMILAR TO
Author Maxi
Post date 2016-04-11T11:57:28Z
Hi,

Which is the better approach to make a case insensitive SIMILAR TO query ?

Example:

CREATE TABLE LOOKUP_ARTICLE
(
  ID INTEGER NOT NULL,
  HEADLINE VARCHAR(100) NOT NULL,
  PUB_DATE TIMESTAMP NOT NULL,
  AUTHOR_ID INTEGER,
  CONSTRAINT INTEG_354 PRIMARY KEY (ID)
);


INSERT INTO "LOOKUP_ARTICLE" ("HEADLINE", "PUB_DATE", "AUTHOR_ID") 
VALUES ('f', '2016-04-10 18:17:29.4297', NULL);

INSERT INTO "LOOKUP_ARTICLE" ("HEADLINE", "PUB_DATE", "AUTHOR_ID") 
VALUES ('fo', '2016-04-10 18:17:29.4297', NULL);

INSERT INTO "LOOKUP_ARTICLE" ("HEADLINE", "PUB_DATE", "AUTHOR_ID") 
VALUES ('foo', '2016-04-10 18:17:29.4297', NULL);

INSERT INTO "LOOKUP_ARTICLE" ("HEADLINE", "PUB_DATE", "AUTHOR_ID") 
VALUES ('fooo', '2016-04-10 18:17:29.4297', NULL)

INSERT INTO "LOOKUP_ARTICLE" ("HEADLINE", "PUB_DATE", "AUTHOR_ID") 
VALUES ('hey-Foo', '2016-04-10 18:17:29.4297', NULL);


Result set
121 f 10.04.2016, 18:17:29.429 [null]
122 fo 10.04.2016, 18:17:29.429 [null]
123 foo 10.04.2016, 18:17:29.429 [null]
124 fooo 10.04.2016, 18:17:29.429 [null]

Then (with SIMILAR TO 'fo*'), 

SELECT  "LOOKUP_ARTICLE"."ID", "LOOKUP_ARTICLE"."HEADLINE", "LOOKUP_ARTICLE"."PUB_DATE", "LOOKUP_ARTICLE"."AUTHOR_ID" 
FROM "LOOKUP_ARTICLE" 
WHERE "LOOKUP_ARTICLE"."HEADLINE" SIMILAR TO 'fo*'
ORDER BY "LOOKUP_ARTICLE"."PUB_DATE" DESC, "LOOKUP_ARTICLE"."HEADLINE" ASC

Return: 
f
fo
foo
fooo

Now, I like to do an expresion to retrive:

f
fo
foo
fooo
hey-Foo

Any advice?

Regards
--
Maxi