How to check if a string contains a value in the database?

Question

Suppose I have a database with:

John Smith - 103

Jane Doe - 287

Mike Smith - 371

And I have a string like:

"Let's see the value of John Smith in the database"

I need a way to check if this string has any of the names in it and get the value associated with the name.

How can I do this in sqlite?

This is not the same as the question linked, it's the inverse. I have the string and I am looking for a pattern that matches it.

If I did:

SELECT *
FROM TABLE
WHERE column LIKE '%string%'

I would get nothing.

It would be:

SELECT *
FROM TABLE
WHERE string LIKE '%column%'

But I don't think this can be done like this.

Possible duplicate of [SQLite string contains other string query](https://stackoverflow.com/questions/3498844/sqlite-string-contains-other-string-query) — juergen d, May 04 '19 at 06:29
You are pointing to the reverse problem. LIKE would solve if I had the pattern and was looking the string that contains it. But I have a string and I am trying to find a pattern that matches it. — José Neto, May 04 '19 at 07:22
What is `John Smith - 103`? Is it a value of 1 column or 2 columns? — forpas, May 04 '19 at 09:45

score 0 · Accepted Answer · answered May 04 '19 at 12:47

You can use sqlite instr:

instr(X,Y)

The instr(X,Y) function finds the first occurrence of string Y within string X and returns the number of prior characters plus 1, or 0 if Y is nowhere found within X. Or, if X and Y are both BLOBs, then instr(X,Y) returns one more than the number bytes prior to the first occurrence of Y, or 0 if Y does not occur anywhere within X. If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs then both are interpreted as strings. If either X or Y are NULL in instr(X,Y) then the result is NULL.

Something like:

SELECT * from table
WHERE instr("Let's see the value of John Smith in the database",name) <> 0

You might want to lower() or upper() all strings to handle case.

That's it, I didn't know the instr function. Thank you very much. — José Neto, May 04 '19 at 15:00

How to check if a string contains a value in the database?

1 Answers1