I am working on a android application with sqlite database that store lots of standard english texts that have uppercase and lowercase words and it shows on the app, we have a search that right now is case sensitive and scroll between them with cursor
How I can return search string case insensitive from sqlite
this is my code on database
public List<String> getSearchedParagraph(String p) {
String sectionId = "";
List<String> result = new ArrayList<String>();
String query = "SELECT Distinct(sectionId) FROM paragraph where txt Like '%"
+p+ "%'";
Cursor cursor = db.rawQuery(query, null);
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
result.add(cursor.getString(0));
cursor.moveToNext();
}
}
return result;
}
i tested String query = "SELECT Distinct(sectionId) FROM paragraph where txt Like '%" +p+ "%' COLLATE NOCASE"; but still no change
UPDATE1 this is setWordSearch function:
public void setWordSearch(String str) {
wordSearch = str;
for (FragmentPage page : fragments) {
page.wordSearch = str;
}
notifyDataSetChanged();
}
UPDATE2 This is the FragmentPage:
public class FragmentPage extends Fragment {
VasiatClass v;
public String SectionId;
public List<VasiatClass> paragraph;
public String wordSearch;
private ScrollView sv;
static MediaPlayer player = new MediaPlayer();
View view;
private List<RelativeLayout> jtxtvwList = new ArrayList<RelativeLayout>();
RelativeLayout rl = null;
float position_x = 0, position_y = 0;
private int playingParagraphPosition;
@Override
public void onAttach(Activity activity) {
super.onAttach(activity);
this.SectionId = String.valueOf(getArguments().getInt("sectionId"));
this.wordSearch = getArguments().getString("wordSearch");
Log.i("FRAGMENTPAGE",
"sectionId is :"
+ String.valueOf(getArguments().getInt("sectionId")));
DataBaseHelper myDbHelper = new DataBaseHelper(this.getActivity());
myDbHelper = new DataBaseHelper(this.getActivity());
wordSearch = getArguments().getString("wordSearch");
try {
myDbHelper.createDataBase();
myDbHelper.openDataBase();
this.paragraph = myDbHelper.getParagraph(SectionId);
myDbHelper.close();
} catch (IOException ioe) {
throw new Error("Unable to create database");
}
};
public String getSection() {
return SectionId;
}
public FragmentPage() {
// TODO Auto-generated constructor stub
}
public View onCreateView(LayoutInflater inflater, ViewGroup container,
Bundle savedInstanceState) {
View view = inflater.inflate(R.layout.fragment_vasiatpage, container,
false);
this.view = view;
LinearLayout ll = (LinearLayout) view.findViewById(R.id.llId);
ll.setPadding(20, 20, 20, 20);
LinearLayout.LayoutParams linLayoutParam = new LinearLayout.LayoutParams(
LinearLayout.LayoutParams.FILL_PARENT,
LinearLayout.LayoutParams.WRAP_CONTENT);
linLayoutParam.setMargins(20, 20, 20, 20);
sv = (ScrollView) view.findViewById(R.id.scrollView1);
for (int i = 0; i <= paragraph.size() - 1; i++) {
final int j = i;
final RelativeLayout rel = new RelativeLayout(getActivity());
// Button b = new Button(getActivity());
// b.setBackgroundColor(Color.TRANSPARENT);
// b.setTextColor(Color.TRANSPARENT);
JustifiedTextView textView = new JustifiedTextView(getActivity());
/************************************/
textView.setOnTouchListener(new OnTouchListener() {
public final static int FINGER_RELEASED = 0;
public final static int FINGER_TOUCHED = 1;
public final static int FINGER_DRAGGING = 2;
public final static int FINGER_UNDEFINED = 3;
private int fingerState = FINGER_RELEASED;
@Override
public boolean onTouch(View arg0, MotionEvent arg1) {
int action = arg1.getAction();
boolean check = true;
switch (action) {
case MotionEvent.ACTION_DOWN:
position_x = arg1.getX();
position_y = arg1.getY();
Log.i("ACTIONS", "Ation down -- position_x:"
+ position_x + " ,position_Y" + position_y);
check = true;
if (fingerState == FINGER_RELEASED)
fingerState = FINGER_TOUCHED;
else
fingerState = FINGER_UNDEFINED;
break;
case MotionEvent.ACTION_MOVE:
if (fingerState == FINGER_TOUCHED
|| fingerState == FINGER_DRAGGING)
fingerState = FINGER_DRAGGING;
else
fingerState = FINGER_UNDEFINED;
check = false;
break;
case MotionEvent.ACTION_UP:
Log.i("ACTIONS",
"Ation up -- position_x:" + arg1.getX()
+ " ,position_Y" + arg1.getY());
if (Math.abs((arg1.getX() - position_x)) < 10
&& Math.abs((arg1.getY() - position_y)) < 10) {
List<String> audioList = new ArrayList<String>();
playingParagraphPosition = j;
((VasiatText) getActivity())
.playParagraph(paragraph.get(j)
.getFilename() + "_MP3.mp3", j,
FragmentPage.this);
if (rl != null) {
rl.setBackgroundColor(Color.TRANSPARENT);
}
rel.setBackgroundColor(Color.LTGRAY);
rl = rel;
}
break;
default:
fingerState = FINGER_UNDEFINED;
break;
}
return false;
}
});
/********************************************/
// textView.getSettings().setFixedFontFamily("Swissra Light.otf");
String str = paragraph.get(i).getTxt().toString();
int subStringStart = 0;
int startIndex = 0;
StringBuilder sb = new StringBuilder();
if (wordSearch != null && !wordSearch.equals("")) {
startIndex = str.indexOf(wordSearch);
while (startIndex != -1) {
sb.append(str.substring(subStringStart, startIndex)
+ "<span style=\"background-color:#ffff00\">"
+ str.substring(startIndex,
startIndex + wordSearch.length())
+ "</span>");
subStringStart = startIndex + wordSearch.length();
startIndex = str.indexOf(wordSearch, subStringStart);
}
sb.append(str.substring(subStringStart));
}
if (!sb.toString().equals("")) {
if (android.os.Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB) {
textView.setText(sb.toString());
} else
textView.setTextNoJustify(sb.toString());
// b.setText(sb.toString());
} else {
if (android.os.Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB) {
textView.setText(str);
} else
textView.setTextNoJustify(str);
// b.setText(str);
}
textView.setId(i);
textView.setTag(Integer.valueOf(paragraph.get(i).getId()));
rel.addView(textView);
jtxtvwList.add(rel);
ll.addView(rel);
}
return view;
}
thanks
