Passing string variable to the Postgres LIKE query

Question

In my flask app I am trying to pass a string to the query below:

tag = "Abcd"

conn = get_db_connection()
cur = conn.cursor()
cur.execute("SELECT * FROM database WHERE message LIKE %s ESCAPE ''", (tag,))
data = cur.fetchall()
cur.close()
conn.close()

I receive blank result.

I use psycopg2. My Postgres database has message containing "Abcd" and it works fine if I simply do:

cur.execute("SELECT * FROM database WHERE message LIKE '%Abcd%'")

If I try to pass it as a variable "tag" is my syntax for LIKE query correct?

You must add the percent signs then: `tag = "%Abcd%"`. – Michael Butscher Feb 19 '23 at 01:17 — Michael Butscher, Feb 19 '23 at 01:17
`LIKE '%%'||%s||'%%'`. – Adrian Klaver Feb 19 '23 at 16:51 — Adrian Klaver, Feb 19 '23 at 16:51

score 0 · Answer 1 · answered Feb 19 '23 at 03:18

you need to add % in your tag.

Try this:

tag = '%Abcd%' #--->> change here, add %Abcd%

conn = get_db_connection()
cur = conn.cursor()
cur.execute("SELECT * FROM database WHERE message LIKE %s ESCAPE ''", (tag,))
data = cur.fetchall()
cur.close()
conn.close()

Passing string variable to the Postgres LIKE query

1 Answers1