Ask Your Question
1

i need Exact match of word in search

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 close merge delete

2 Answers

Sort by » oldest newest most voted
1

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:

SELECT
    "Table".*
FROM
    "Table",
    "Table_filter"
WHERE
(
    "Table"."Name" LIKE 'Mark'
    AND
    "Table_filter"."Name" LIKE 'Mark'
) OR "Table_filter"."Name" IS NULL
;
edit flag offensive delete link more
0

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

Comments

Sorry, I spoke too soon, my bad.

rautamiekka gravatar imagerautamiekka ( 2016-11-30 22:50:42 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 275 times

Last updated: Nov 28 '16