0

My original code is : 

 c = mydb.rawQuery("select dId,dWord FROM german where dWord like '%" + search + "%'", null);

and it returned complete records like below photo. enter image description here

I changed code to follow code but it also had problem . 

c = mydb.rawQuery("select dId, case when substr(dWord, 1, 4) in ('das ', 'der ','die ') then substr(dWord, 5, length(dWord) - 4) else dWord end as dWord from german where dWord like '%"+search+"%'",null);

It doesn't show 4 letters and it is good, but it shows the records that had this prefix( 'das ', 'der ','die ') and it shouldn't be like this. It must return records just contain searched characters . In photo you can see it returned records that had prefix .All these words started with 'das ' whit search 'das 'after second code it should returned words that contains das on their main body not on removed prefix . I hope I could explain it clearly .

forpas
  • 160,666
  • 10
  • 38
  • 76
Shayegan
  • 37
  • 8

1 Answers1

1

In SQLite the concatenation operator is || and not +.
Also don't concatenate directly the parameter search but use a ? placeholder and pass search at the 2nd argument of rawQuery(). This is the safe way to avoid sql-injection:

String sql = "select dId, " + 
   "case when substr(dWord, 1, 4) in ('das ', 'der ','die ') " + 
   "then substr(dWord, 5) else dWord end as dWord " +
   "from german where dWord like '%' || ? || '%'";
c = mydb.rawQuery(sql, new String[] {search});


If that dWord column in the WHERE clause is the result of the CASE expression then change the statement to this:

String sql = "select t.* from (select dId, " + 
   "case when substr(dWord, 1, 4) in ('das ', 'der ','die ') " + 
   "then substr(dWord, 5) else dWord end as dWord " +
   "from german) t where t.dWord like '%' || ? || '%' " +
   "order by instr(t.dWord, ?), t.dWord"
c = mydb.rawQuery(sql, new String[] {search, search});
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks but,it didn't change anything and it returned records like first photo . – Shayegan Nov 14 '19 at 18:57
  • The code works. Check this simplified demo: https://www.db-fiddle.com/f/pxtR4jq4AJ8R8zvveu2GoT/1 I can't tell how you use it in your code. – forpas Nov 14 '19 at 19:08
  • Also this: https://www.db-fiddle.com/f/pxtR4jq4AJ8R8zvveu2GoT/2 for search pattern `'das '` – forpas Nov 14 '19 at 19:11
  • Clarify something: *it should returned words that contains das on their main body not on removed prefix* does this mean that the search pattern is for the remaining part of the dWord after the removal of the 4 characters? – forpas Nov 14 '19 at 19:14
  • My English is not so good and I can not explain it very well . I tried to explain with example . I have these words on my data base : 1- das apartment 2- das Gluck 3- die Hand 4- dashboard 5- dasselbe 6- alandas 7-dermatologe 8-irmander 9-trandie 10 - der tisch and after run query and filter these words ('das') it should show this records : 1- dashboard 2- dasselbe 3- alandas or if I filter ('der') : 1- irmander – Shayegan Nov 14 '19 at 19:30
  • This is what my 2nd query does. Check the demo: https://www.db-fiddle.com/f/pxtR4jq4AJ8R8zvveu2GoT/3 – forpas Nov 14 '19 at 19:37
  • is it possible to sort returned data based on searched characters ? for example if I searched for 'ap' then in returned data at first it show records that started with 'ap' and then records that 'ap' is in the middle of word because I want to use it for dictionary .eg : search input : 'ap' result : Apartment Apollo apin rap kapeloo .... – Shayegan Nov 14 '19 at 20:36
  • Thanks a lot .It is work but it didn't sorted word alphabetically .eg : searched 'ap' apartment apbrment apcrment – Shayegan Nov 14 '19 at 20:55
  • I edited to sort alphbetically **after** the 1st sorting. – forpas Nov 14 '19 at 20:57
  • In Apartment Apollo apin rap kapeloo the results are not sorted alphabetically though. – forpas Nov 14 '19 at 20:59
  • I am a little dumb , sorry for it and thank you very much :)))) – Shayegan Nov 14 '19 at 21:04
  • I asked new question about make this code no case sensitive on follow link . Maybe you can help . Thanks : https://stackoverflow.com/questions/58874356/how-to-make-my-sqlite-query-case-insensitive/ – Shayegan Nov 15 '19 at 10:23