[firebird-support] Case insensitive SIMILAR TO

 

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

__._,_.___

---

Posted by: Maxi <maxirobaina@gmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 4

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___