Javascript Array to MySQL "In" list of values

Question

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.

It gives escape character along with single quotes. Tried already — Prerna Jain
– Prerna Jain, Commented Apr 2, 2017 at 8:19
late to party, but your code output is different than provided result - it may be missing something — barbsan
– barbsan, Commented Jan 25, 2019 at 8:59

Nina Scholz · Accepted Answer · 2017-04-02 08:05:10Z

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);

edited Apr 2, 2017 at 8:05

answered Apr 2, 2017 at 8:04

Nina Scholz

388k26 gold badges367 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mplungjan Over a year ago

I think this is simpler: var ids = "'"+userIds.join("','")+"'"

Nina Scholz Over a year ago

you need to quote single quotes in the string as well.

Prerna Jain Over a year ago

With this solution too, m getting resultant string as '\'Ab526\',\'shvx23\',\'23636dsd\''

Nina Scholz Over a year ago

you may have a look here: stackoverflow.com/questions/7744912/…

Olubodun Agbalaya · Accepted Answer · 2021-08-10 16:04:54Z

3

Or a simple one liner

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

console.log(query)

edited Aug 10, 2021 at 16:04

answered Aug 10, 2021 at 15:57

Olubodun Agbalaya

5135 silver badges11 bronze badges

Comments

dev8080 · Accepted Answer · 2017-04-02 08:20:24Z

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 );

answered Apr 2, 2017 at 8:20

dev8080

4,0051 gold badge14 silver badges18 bronze badges

2 Comments

Prerna Jain Over a year ago

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\')'

dev8080 Over a year ago

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.

mahip_j · Accepted Answer · 2017-04-02 08:33:01Z

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);

edited Apr 2, 2017 at 8:33

answered Apr 2, 2017 at 8:21

mahip_j

3682 silver badges11 bronze badges

1 Comment

Prerna Jain Over a year ago

this is exactly similar to what array.prototype.join() does.

Akshay Malik · Accepted Answer · 2019-09-19 03:27:47Z

-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

edited Sep 19, 2019 at 3:27

answered Sep 19, 2019 at 3:22

Akshay Malik

11 bronze badge

1 Comment

GavinBelson Over a year ago

Does not work for strings in array like in the question

Andrey · Accepted Answer · 2020-02-25 11:26:40Z

-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(",") + ")"

edited Feb 25, 2020 at 11:26

answered Feb 24, 2020 at 10:52

Andrey

1411 silver badge4 bronze badges

1 Comment

chevybow Over a year ago

Can you edit your answer with a textual explanation of the code for future readers please?

Collectives™ on Stack Overflow

Javascript Array to MySQL "In" list of values

6 Answers 6

4 Comments

Comments

2 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

2 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related