I am trying to pass array of cities to the IN clause but seems inverted commas are removed while preparing query
Here is the code
let city = ["Moscow", "Paris"];
let sql = 'SELECT* FROM cities WHERE city IN ('+city+')';
console.log(sql);
and in the console I am getting this
SELECT* FROM cities WHERE city IN (Moscow, Paris);
and thus getting exception while executing query!
Any help?
3 Answers 3
Try this, it will work
var sql = "SELECT uid FROM cities where city IN ('" + gender.join("','") + "')";
Result :-
SELECT uid FROM cities where city IN ('Male','Female')
Sign up to request clarification or add additional context in comments.
Comments
Using a Query Builder is a preferable method but a possible solution if you have a long array:
let stringifiedValues = JSON.stringify(city).replace(/\[|\]/g,'');
let sql = `SELECT * FROM cities WHERE city IN (${stringifiedValues})`;
1 Comment
Alex
This is an interesting solution for this task! If you don't like RegExp, you can use
JSON.stringify(city).slice(1, -1)- Use the map function to transform all items in your array. You want to add quotes to the items like this
Moscow=>'Moscow' - Use the join to gather the transformed items into a string. The delimiter can be specified as an argument like this
array.join(', ') - Use template literals (string interpolation) to inject your string in your query. Like this
${str}
const city = ["Moscow", "Paris"];
const injectedString = city.map(c => `'${c}'`).join(', ');
let sql = `SELECT * FROM cities WHERE city IN (${injectedString})`;
console.log(sql);
answered Feb 8, 2019 at 13:40
molamk
4,1161 gold badge15 silver badges22 bronze badges
Comments
default
+. Try withjoinon the array instead to build your string first