How to use 'like' in Anorm query?

Question

My Data base is porstgres, and I am trying to write a query that will use like or ~ (postgres regex). I couldn't write this query using on.

so here is what I tried to write:

SQL(s"""SELECT id, name
       |FROM my_table 
       |WHERE name ~ '^{name}'""")
.on('name -> name).as(parser.*)

Or:

SQL(s"""SELECT id, name
       |FROM my_table 
       |WHERE name like '{name}%'""")
.on('name -> name).as(parser.*)

The code that is working for me does not use on. And it looks like this:

SQL(s"""SELECT id, name
       |FROM my_table 
       |WHERE name """.concat(s"like '$name%'").as(parser.*)

Is there a better way to achieve this?

Answer 1

When having a ... '{param}' with a string value passed using .on, the JDBC statement is prepared as ... ''$strVal'', as the value is escaped according JDBC.

So the the single quotes should be removed from the SQL statement, and the %, must be part of the escaped string value.

SQL("""SELECT id, name
   |FROM my_table 
   |WHERE name like {name}""")
  .on('name -> s"{name}%").as(parser.*)

Moreover, there the String Interpolation is useless (SQL(s"...") should be SQL("...")), as Anorm placeholder are not prepared using that.

If you want to use interpolation, it would be better to use Anorm one.

SQL"""SELECT id, name
   |FROM my_table 
   |WHERE name like ${name + "%"}""".
  as(parser.*)