Space in SQLite query

Question

WHERE g.value like '" + lookingFor + "%'

I have an EditText which filters cursor from SQLite database. I want to take 'value' from 'g' table. For example one of 'value' is 'full stop'. When I input 'stop', query should find full stop.

If lookingFor is "efg", nothing is found. How to find "abcd efgh" when "efg" is typed?

PS. I do not want to get "abcd edfh" when I type "cd"

I think you might need a % at the start of the string as well, such as like '%" + lookingFor + "%'. Your code would be looking for a field that starts with efg and ends in anything — CurlyPaul, Aug 13 '13 at 09:41
Unfortunately, it will find "abcd efgh" when I type "cd", right — Joe Rakhimov, Aug 13 '13 at 09:47
It will. Didn't see your edit until after I posted. So why not include the space in the query eg LIKE '% " + lookingFor + "%'. This should find a field that has some characters at the start, followed by a space, followed by what you typed — CurlyPaul, Aug 13 '13 at 09:54

score 3 · Answer 1 · answered Aug 13 '13 at 09:42

3

Try using
WHERE g.value like '%" + lookingFor + "'

answered Aug 13 '13 at 09:42

hich9n

1,578
2
15
32

Unfortunately, it will find "abcd efgh" when I type "cd", right? – Joe Rakhimov Aug 13 '13 at 09:46

ClaireG · Answer 2 · 2013-08-13T09:55:32.910

1

The % before the lookingFor in sql represents that there might be other data before lookingFor. % after the lookingFor represents that there might be other data after lookingFor.

So if you want to find "efg" when you have data like "abcd efgh" you need to do:

....WHERE g.value LIKE '%" + lookingFor + "%' ";

And yes unfortunately it will find "abcd.." when you type "cd". In order to fix this you can check the value of lookingFor before you actually execute the sql command.

if(lookingFor.equals("efg")
{
    ....WHERE g.value LIKE '%" + lookingFor + "%' ";
}
else
{
     //Do whatever you want here if the value is not "efg".
}

Good luck

edited Aug 13 '13 at 09:55

answered Aug 13 '13 at 09:50

ClaireG

1,244
2
11
23

what about doing like this: before query String strSpace=" " – Joe Rakhimov Aug 13 '13 at 09:53
WHERE g.value LIKE 'strSpace + lookingFor + "%' " – Joe Rakhimov Aug 13 '13 at 09:54
The extra space you enter is just being added to lookingFor, so now lookingFor is " efg", that will lead you to the problem you had from the start; i.e. it will not fix the problem – ClaireG Aug 13 '13 at 09:57

Harish Godara · Answer 3 · 2013-08-13T09:54:08.977

0

Try this one :

Cursor oCursor = myDatabasequery(true, DATABASE_TABLE, new String[]{column1, column2,column3},  column2+ " LIKE '%" + lookingFor + "%'", null, null, null, null, null);

In your code :

... WHERE g.value LIKE '%" + lookingFor + "%' "

It will search your string whatever before and after that

edited Aug 13 '13 at 09:54

answered Aug 13 '13 at 09:41

Harish Godara

2,388
1
14
28

How to do it in raw query? – Joe Rakhimov Aug 13 '13 at 09:45
I have edited please check – Harish Godara Aug 13 '13 at 09:54

score 0 · Accepted Answer · answered Aug 13 '13 at 10:02

0

Before query:

String strSpace=" ";

Inside query:

 "...   WHERE g.value like '"+ lookingFor + "%' OR g.value like '%"+strSpace + lookingFor + "%'   ..."

answered Aug 13 '13 at 10:02

Joe Rakhimov

4,713
9
51
109

Space in SQLite query

4 Answers4