Using LIKE statement in SQLite 3 from Objective-C

If you aren't that familiar with SQLite 3 and are wondering how to construct statements with the LIKE operator and use NSStrings when you bind to a prepared statement, read on.

Whether you are familiar with Microsoft T-SQL or another variant of the Structured Query Language(SQL), you are probably familiar with a statement like the following:
SELECT * FROM cities WHERE name LIKE 'new%' OR alternateNames LIKE '%new%' ORDER BY population DESC

Now if you want to execute that statement from a Cocoa Touch app running on an iPhone or iPod touch, you have to sort out each of the following:

• How can the LIKE terms be parameterized?
• Do I have to escape the NSString in single quotes?
• How do I put the % symbol in the string?

This may not be the only, easiest, or best way, but it is the one I am using in iTimeZone 1.1.

First, this is how the statement has to be constructed for parameterization:
SELECT * FROM cities WHERE name LIKE ?001 OR alternateNames LIKE ?002 ORDER BY population DESC

Notice that the parameter tokens, ?001 and ?002 do not have quotes around them. This statement needs to be prepared in the usual way.

Next, you have to create new strings that put the % wildcard character into what you are actually looking for:

NSString *wildcardSearch = [NSString stringWithFormat:@"%@%%", cityName];
NSString *wildcardAlternateSearch = [NSString stringWithFormat:@"%%%@%%", cityName];

Notice the %% characters in the format string. This results in one % in the output string. %@ is the replacement token for your string parameter.

Finally, you have to bind your strings to the prepared statement like this:

sqlite3_bind_text(findTimeZone_statement, 1, [wildcardSearch UTF8String], -1, SQLITE_STATIC);
sqlite3_bind_text(findTimeZone_statement, 2, [wildcardAlternateSearch UTF8String], -1, SQLITE_STATIC);

The parameters are:

1. The prepared statement
2. The variable number to bind to
3. The variable data
4. The string length. -1 means determine length from the string input
5. The kind of variable, determining if the API makes a copy of the variable or not. SQLITE_STATIC does not, SQLITE_TRANSIENT does

That is how I have solved the problem. Hope it helps you out.