7

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?

Karlom
  • 13,323
  • 27
  • 72
  • 116

3 Answers3

10

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.

Martin Tournoij
  • 26,737
  • 24
  • 105
  • 146
Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
4

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!

QuarK
  • 2,306
  • 1
  • 20
  • 24
1

You can use SQL string concatenation:

SELECT * FROM author
WHERE first_name LIKE ? || '%' OR last_name LIKE ? || '%'
Ainar-G
  • 34,563
  • 13
  • 93
  • 119
  • Yes. But then the SQL would be less flexible (say you wanted to search for an exact match or another wildcard pattern). – Cetin Basoz Oct 14 '16 at 11:28