How to Concat_Ws Multiple Fields and Remove Duplicate Separators for Empty Slots

How to concat_ws multiple fields and remove duplicate separators for empty slots

Do it like this:

CONCAT_WS(' ', NULLIF(field1, ''), NULLIF(field2, ''), NULLIF(field3, ''));

CONCAT_WS will skip any null values, and by using NULLIF any empty ones too.

Note: You can't regex replace. MySQL does not support it.

Get an extra comma ',' while using concat

By using an IF:

UPDATE recipes_new 
SET co_auths = IF(co_auths IS NULL, c1id, CONCAT(co_auths, ',', c1id))
WHERE id = name_in; 

If the value of co_auths is an empty string instead of NULL:

UPDATE recipes_new 
SET co_auths = IF(LENGTH(co_auths), CONCAT(co_auths, ',', c1id), c1id)
WHERE id = name_in; 

Create an array with same element repeated multiple times

You can do it like this:

function fillArray(value, len) {
  if (len == 0) return [];
  var a = [value];
  while (a.length * 2 <= len) a = a.concat(a);
  if (a.length < len) a = a.concat(a.slice(0, len - a.length));
  return a;
}

It doubles the array in each iteration, so it can create a really large array with few iterations.

---

Note: You can also improve your function a lot by using push instead of concat, as concat will create a new array each iteration. Like this (shown just as an example of how you can work with arrays):

function fillArray(value, len) {
  var arr = [];
  for (var i = 0; i < len; i++) {
    arr.push(value);
  }
  return arr;
}

---

Related Topics