We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

i need Exact match of word in search [closed]

asked 2016-11-27 11:17:42 +0200

koolninja gravatar image

i used this query of searching name

SELECT "Table".* FROM "Table", "Table_filter" WHERE ( ( ( UPPER ( "Table"."Name" ) LIKE '%' || UPPER ( "Table_filter"."Name" ) || '%' ) OR ( "Table_filter"."Name" IS NULL ) )

Example ;- i search Mark it returns Mark, Mark Pop, Rock Mark but i want to search only Name Mark or if i search Rock Mark it should return only Rock Mark.

i tried many option but all not solved my purpose.

Thanks in advance

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-15 23:47:06.734751

2 Answers

Sort by » oldest newest most voted

answered 2016-11-27 11:55:29 +0200

updated 2016-11-27 12:17:00 +0200

There's 1 problem: HSQLDB 1.8 defaults to case-sensitive columns, so even with a correct query you wouldn't get all Marks.

You have 2 options:

1) Create a new db, execute SET IGNORECASE TRUE; and make the tables. The official doc doesn't make it clear whether it's every single db made on that computer or just the new one you just created. It does say only an Admin can do that.

2) Re-make the tables and set the Text columns to VARCHAR_IGNORECASE.

This query worked for me after that:

    "Table"."Name" LIKE 'Mark'
    "Table_filter"."Name" LIKE 'Mark'
) OR "Table_filter"."Name" IS NULL
edit flag offensive delete link more

answered 2016-11-28 16:57:34 +0200

koolninja gravatar image

I change text VARCHAR to Text CHARACTER

for "Table_filter" ."Name ", "Table"."Name" . It's

work good for Exact match of word

edit flag offensive delete link more


Sorry, I spoke too soon, my bad.

rautamiekka gravatar imagerautamiekka ( 2016-11-30 22:50:42 +0200 )edit

Question Tools

1 follower


Asked: 2016-11-27 11:17:42 +0200

Seen: 838 times

Last updated: Nov 28 '16