How to use wildcard in sqlx query in Go?

Question

I'm using sqlx with mysql database and want to query author table for names which start with certain letters. Here is the query:

sqlx.DB.Select(&authors, "SELECT * FROM author WHERE first_name LIKE ?% OR last_name LIKE ?%", letter,letter)

but I get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '% OR last_name LIKE ?%'

I've looked in the docs for examples of LIKE queries but could not find any. So wondering how can I fix this?

Answer 1

You have % in there, wouldn't those be ?, ? or :p1, :p2, $1, $2 (whatever the MySQL parameter placeholder is)

I would do like this:

sqlx.DB.Select(&authors, "SELECT * FROM author WHERE first_name LIKE ? OR last_name LIKE ?",
    letter+"%",letter+"%")

The wildcard % should be part of the parameter string.

Answer 2

Another way to do it is using CONCAT:

sqlx.DB.Select(&authors, "SELECT * FROM author WHERE first_name LIKE CONCAT(?, '%') OR last_name LIKE CONCAT(?, '%')", letter, letter)

Hope it helps!

Answer 3

You can use SQL string concatenation:

SELECT * FROM author
WHERE first_name LIKE ? || '%' OR last_name LIKE ? || '%'