5

I have an array where each element corresponds to an alphanumeric string, lets say :

userIds : ['Ab526', 'shvx23', '23636dsd']

I want to convert it into such a format so that I can pass this list of strings to IN clause of mySQL query as something like :

Select * from Users where userIds in '(...)';

I tried using array.join() and many other methods, suggested somewhere or the other but all in vain.

var userIds = ['Ab526', 'shvx23', '23636dsd']
var ids    = userIds.join(',');
var query  = 'Select * from Users where userIds in (' + ids + ')'; 
console.log(query);

which results in :

Select * from Users where userIds in ('Ab526, shvx23, 23636dsd');

If anyone could suggest a solution as how can I achieve what I want to, it would be of great help.

barbsan
  • 3,418
  • 11
  • 21
  • 28
Prerna Jain
  • 1,240
  • 2
  • 16
  • 34

6 Answers6

3

You could map the quoted values and join later.

var userIds = ['Ab526', 'shvx23', '2363\'6dsd'],
    result = userIds.map(function (a) { return "'" + a.replace("'", "''") + "'"; }).join();
    
console.log(result);
Nina Scholz
  • 376,160
  • 25
  • 347
  • 392
2

You could use reduce on the array: 

var userIds = ['Ab526', 'shvx23', '23636dsd'];
    var clause = userIds.reduce(
                 function (cl , a, currIndex, arr) 
                     { 
                     return cl + 
                      (currIndex == 0 ? "" : ",")  
                     +"'"+ a + "'"+ 
                     (currIndex == arr.length-1 ? ")" : "") ; } , "(" );
    
    console.log(clause );
dev8080
  • 3,950
  • 1
  • 12
  • 18
  • As a string it comes out fine, but when appending it to SQL Query string, it throws an error : error: ER_PARSE_ERROR: 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 "(\'Ab526\',\'shvx23\',\'23636dsd\')' – Prerna Jain Apr 02 '17 at 09:04
  • I know it's trivial, but do you add a space between 'in' and '( ... ' ? Many of my errors in query concat were due to missing spaces. – dev8080 Apr 02 '17 at 10:22
2

You can use the following code :

var userIds = ['Ab526', 'shvx23', '23636dsd'];
var ids = '';
userIds.forEach(function(entry,index) {
    ids += (index == 0) ? entry : ',' + entry;
});
console.log(ids);
mahip_j
  • 368
  • 2
  • 11
2

Or a simple one liner

const userIds = [12,13,14,15];
const query  = `Select * from Users where userIds in ('${userIds.join("','")}')`; 

console.log(query)
Olubodun Agbalaya
  • 488
  • 4
  • 10
-1

This worked for me for the same issue:

var myList = [1, 2, 3]
var listAsString = myList.toString() 
var queryList = "(" + listAsString + ")"

SQL Query is like so: WHERE number IN queryList

Akshay Malik
  • 1
  • 1
-1
let tickers = ['AAPL', 'MSFT']
const inStmt = "('" + tickers.join("','") + "')"

This will give you the result inStmt == ('AAPL','MSFT') which can be later on used as: 

const QUERY = `select x from Y where Y.z in ${inStmt}`

Please note, that if you have numbers, not strings, single quotes must be removed:

let tickers = [1, 2, 3]
const inStmt = "(" + tickers.join(",") + ")"
gosuer1921
  • 101
  • 1
  • 4