write a query to remove 4 first letters on special records in sqlite

Question

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 .

Answer 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});