Friday, August 26, 2011

SQLite3 query for string match.

One interesting question came up when I had to match for strings regardless of case(uppercase, lowercase) in Database

I'm trying to create a query to match all records containing the word "REETU".
The problem is user can entered the search string as  REETU, Reetu, reetu, or REEtu.

Let's say that we have a Employee table with a field called name.

Following query will do
select * from Employee
where upper(
name) like 'REETU';

You can try following query to return all of the records where the name field contains the word "Reetu", regardless of whether it was stored as  REETU, Reetu, reetu, or REEtu regardless of  search string which can be either  REETU, Reetu, reetu, or REEtu.
select * from Employee
where upper(
name) like upper('Reetu')

FOR more information on LIKE , you can visit this link which I found really helpful.