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
