CODEWITHSUNDEEP
node.jspostgresql
xyz83242
xyz83242

Reputation: 824

Postgres Node search query using LIKE, how to set %

I have a weird problem I encountered using Postgresql and Node. I would like to use LIKE in my query together with % at the beginning and end of searched term. I have no issue using it in the plain SQL:

THIS WORKS:

SELECT * FROM vehicle WHERE module_imei LIKE '%searchterm%' OR custom_id LIKE '%searchterm%'

However, using it in Node is a bit of challenge. I haven't been successful in resolving it yet: THIS DOES NOT WORK:

  getVehiclesSearch: async function({ search }) {
    let response;
    try {
      response = await pool.query(`SELECT * FROM vehicle WHERE module_imei LIKE %$1% OR custom_id LIKE %$1%`, [search]);
      if(response) return response.rows;
    } catch(error) {
      // handle error
      console.error(error);
      // do not throw anything
    }
  },

Doing above will produce: syntax error at or near "%"

SELECT * FROM vehicle WHERE module_imei LIKE '%${$1}%' OR custom_id LIKE '%${$1}%

Doing above will produce: $1 is not defined

SELECT * FROM vehicle WHERE module_imei LIKE '%$1%' OR custom_id LIKE '%$1%'

Doing above will produce: bind message supplies 1 parameters, but prepared statement "" requires 0

I kind of struggle factoring the % in so it won't crash the query. Simply run out of ideas after trying above and variables of those. Thanks for your kind help.

Upvotes: 0

Views: 1983

Answers (2)

xyz83242
xyz83242

Reputation: 824

This has been already answered over here: Go postgresql LIKE query

In this particular case:

      response = await pool.query(`SELECT * FROM vehicle WHERE module_imei LIKE '%'||$1||'%' OR custom_id LIKE '%'||$1||'%'`, [search]);

This would work.

Upvotes: 2

l2ysho
l2ysho

Reputation: 3073

You are missing single quotes, also I don't recognize a db adapter you use, but you can use template literals (watch out for sql injection!!!)

response = await pool.query(`SELECT * FROM vehicle WHERE module_imei LIKE '%${search}%' OR custom_id LIKE '%${search}%'`);

Upvotes: 0

Related Questions