APriya
APriya

Reputation: 2004

Search query in Sqlite

I am having some items in database. i filter using "like" keyword

Query:mCursor = mDb.query(listTable, new String[] { ID_rs, Item_name, item_code, Unit_code, Tot_Amt, dis_count, }, Item_name + " like '%" + mix + "%' ORDER BY " + Item_name + " ASC", null, null, null, null, null);

Ex: values in the database 1.SAKTHI readymix 2.curry Mix 3.Mixture

my result : 1.SAKTHI readymix 2.curry Mix 3.Mixture

But i need "curry Mix, Mixture" as my output how can i get that. help me out!

Upvotes: 1

Views: 3232

Answers (4)

IAmGroot
IAmGroot

Reputation: 13865

Going on your comments,

You could try using the syntax

Item_name + " like '" + mix + "%' OR " + Item_name + " like '% " + mix + "%'"

Where basically,

  • the first matches ones that start with Item_name,
  • the OR attaches ones that have Space followed by Item_name. Aka are a new word. (note the space after the %)

Upvotes: 2

donfede
donfede

Reputation: 734

given that " @CL i need to search the starting of the string not the words in middle – APriya"

what if you try:

Query:mCursor = mDb.query(listTable, new String[] { ID_rs, Item_name, item_code, Unit_code, Tot_Amt, dis_count, }, Item_name + " like '% " + mix + "%' ORDER BY " + Item_name + " ASC", null, null, null, null, null);

note the space after first %, as the goal is to find only entries that begin with mix.

Upvotes: 1

CL.
CL.

Reputation: 180310

In theory, the easiest way would be to create a full-text search table for the item names, but not all Android version have FTS enabled.

To search for values that begin with mix, use LIKE. To search for values that have mix at the beginning of a word, use GLOB to search for a character that is not a letter in front of mix:

SELECT *
FROM ListTable
WHERE ItemName LIKE 'mix%'
   OR ItemName GLOB '*[^a-zA-Z][mM][iI][xX]*'

Unlike LIKE, GLOB is case sensitive, so you have to list both lower and upper case characters.

If a word can contain any non-ASCII letters, you have to add them to the a-z set.

Upvotes: 1

Moog
Moog

Reputation: 10193

You should use a where clause that uses an OR operator:

WHERE Item LIKE '%to' OR Item LIKE 'to%'

Upvotes: 0

Related Questions