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?

Emblazon

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.

Uranus

Works great. Thank you! – Emblazon

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!

Sheathing

Answer 3

You can use SQL string concatenation:

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

Weksler

Yes. But then the SQL would be less flexible (say you wanted to search for an exact match or another wildcard pattern). – Uranus