CODEWITHSUNDEEP
iphoneobjective-cios4sqlite
Parth Bhatt
Parth Bhatt

Reputation: 19469

SQLite query for finding similar words using the SQLite "like" clause

In my iPhone app, I have requirement to search the SQLite database.

I will search the similar data on the basis of what the user enters into the textbox.

What I want:

I want query like

Select category from categorytable where category like 'A%', if user enters A

I tried

NSString *query = [NSString stringWithFormat:@"Select category from categorytable where category like '%@%'",textbox1.text];

It does not show the "%" after the character entered by the user.

In console it shows like

**Select category from categorytable where category like 'A'**  which is incorrect

What should be the query?

Upvotes: 1

Views: 1804

Answers (3)

Rob
Rob

Reputation: 437612

As others have said, use %% syntax in stringWithFormat to have the literal percent sign appear within the search string. But, do not do that with your SQL. Only do that with the value. One should avoid using stringWithFormat to insert text values directly into the SQL. Instead, using ? placeholders in the SQL:

NSString *value = [NSString stringWithFormat:@"%@%%", textbox1.text];

NSString *query = @"Select category from categorytable where category like ?";

Then prepare your sqlite3_stmt using query and then sqlite3_bind_text with the value. For example:

sqlite3_stmt *statement;
int rc;

if ((rc = sqlite3_prepare_v2(db, [query UTF8String], -1, &statement, NULL)) != SQLITE_OK) {
    NSLog(@"%s (%ld)", sqlite3_errmsg(db), (long)rc);
} else {
    if ((rc = sqlite3_bind_text(statement, 1, [value UTF8String], -1, SQLITE_TRANSIENT)) != SQLITE_OK) {
        NSLog(@"%s (%ld)", sqlite3_errmsg(db), (long)rc);
    }
    while ((rc = sqlite3_step(statement)) == SQLITE_ROW) {
        const unsigned char *string = sqlite3_column_text(statement, 0);
        NSLog(@"%s", string);
    }
    if (rc != SQLITE_DONE) {
        NSLog(@"%s (%ld)", sqlite3_errmsg(db), (long)rc);
    }
    sqlite3_finalize(statement);
}

Or, if using FMDB:

FMResultSet *rs = [db executeQuery:query, value];
NSAssert(rs, @"%@", [db lastErrorMessage]);

while ([rs next]) {
    NSLog(@"%@", [rs resultDictionary]);
}
[rs close];

This is important, to prevent problems stemming from the occurrences of ' characters within the value (e.g., if you're looking for "Bob's Bar and Grill").

Upvotes: 0

Joseph Tura
Joseph Tura

Reputation: 6360

You need to use %% in stringWithFormat.

Upvotes: 2

Dave DeLong
Dave DeLong

Reputation: 243156

The string format specifiers document says that if you want the % literal character to appear in the final string, you should use %% in the format string. So you should be using '%@%%'

Upvotes: 2

Related Questions