How to do NOT in LIKE query in SQLite?

Question

I have a table as follows:

CREATE TABLE Foo(
    id      INT PRIMARY KEY,
    key     VARCHAR,
    value   VARCHAR
)

id       key     value
1        Hi      ABCDE/FGHI
2        Hi      DFGRT
3        Hi      FSEHU/

Pre-existing library function (edited for clarity):

char* Foo_getId(char *value){
    char *query = "SELECT id FROM Foo WHERE value LIKE ?";
    sqlite3_stmt *statement;
    int rc = sqlite3_prepare_v2(connection, query, strlen(query), &statement, NULL);
    rc = sqlite3_bind_text(statement, 1, value, strlen(value), SQLITE_STATIC);
    rc = sqlite3_step(statement);
    return strdup((char*)sqlite3_column_text(statement, 0));
}

My function:

char *getFooIdFromValueWithoutSlash(){
    return Foo_getId("WHAT GOES HERE???");
}

How do create a LIKE query such that it will match any value which does not contain a '/'?

E.g. SELECT * FROM Foo WHERE value LIKE '%!/%'

Note: the reason I am trying to do this is that I am interfacing with someone elses code of which I can only pass the value placed after the like.

You can't do what you're asking. You can't negate a `LIKE` using only the value provided after it; that's not how `LIKE` works. You can negate it by modifying the query to use `NOT LIKE`, but you have to modify the SQL. — Ken White, Jul 09 '13 at 19:35

score 2 · Answer 1 · answered Jul 08 '13 at 21:07

2

You mean this?

SELECT * FROM Foo WHERE value NOT LIKE '%/%'

answered Jul 08 '13 at 21:07

Bill Gregg

7,067
2
22
39

That would be great, but is there an equivalent of `[a-zA-Z0-9]*`? As I said, I cant change the query, I can only pass in the argument passed in for the LIKE. – chacham15 Jul 08 '13 at 21:08
@chacham15 For that you might want to look at http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query – Dan D. Jul 08 '13 at 21:49
@chacham15 Do you want to use a `LIKE` expression or a regular expression? Please edit your question to clarify this! – CL. Jul 09 '13 at 06:55

score 1 · Accepted Answer · answered Jul 09 '13 at 19:38

1

You cannot construct a LIKE pattern that behaves the same as a NOT LIKE pattern; the only special characters are % and _.

That someone else's code is just not capable of doing what you want.

answered Jul 09 '13 at 19:38

CL.

173,858
17
217
259

How to do NOT in LIKE query in SQLite?

2 Answers2