Agfdhyk

Problem

I am doing a database migration and I came across the following senario, I have single quotation mark inside the single quotation marks of the insert and after several attempts to fix I reached the conclusion that I would need to use regex and I arrived to the point where I could replace the single quotation mark in the center of the text, I now have a problem when it appears at the beginning. my regex has the first two single quotation mark but my goal is the last two.

SQL Insert:

(1, '22', '2006-11-10', '3', 'field1', 'field2', '2006-12-12', '11:20:06', '''field3', '



Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus ex ipsum, luctus id dignissim tempor, volutpat eget sapien. Cras accumsan molestie iaculis. Mauris vehicula a nunc vel tincidunt. 

 Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. 



    - Lorem ipsum dolor '' sit amet - ''''



 Aliquam vestibulum tincidunt odio, id varius sapien lacinia ac. Phasellus ullamcorper ante ut lacus dictum commodo.

  Integer sit amet lacus ornare massa feugiat tempus. 



', null, null);

My Regex(js):

((["'])(?:(?=(''?))2)*?)1(?!,)

My goal after regexp and make a find and replace is to have this as a result:

(1, '22', '2006-11-10', '3', 'field1', 'field2', '2006-12-12', '11:20:06', '''field3', '



Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus ex ipsum, luctus id dignissim tempor, volutpat eget sapien. Cras accumsan molestie iaculis. Mauris vehicula a nunc vel tincidunt. 

 Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. 



    - Lorem ipsum dolor '' sit amet - ''''



 Aliquam vestibulum tincidunt odio, id varius sapien lacinia ac. Phasellus ullamcorper ante ut lacus dictum commodo.

  Integer sit amet lacus ornare massa feugiat tempus. 



', null, null);

I am using this site to test: https://regex101.com/ (with javascript selected)

One option would be to match a ' or " delimiter, then lazy-repeat until you match that delimiter again followed by a comma (or a ), in case the item is the last in the list). Then, use a replacer function to replace everything inside the delimiters with 's escaped:

const input = `(1, '22', '2006-11-10', '3', 'field1', 'field2', '2006-12-12', '11:20:06', '''field3', '



Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus ex ipsum, luctus id dignissim tempor, volutpat eget sapien. Cras accumsan molestie iaculis. Mauris vehicula a nunc vel tincidunt. 

 Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. 



    - Lorem ipsum dolor '' sit amet - ''''



 Aliquam vestibulum tincidunt odio, id varius sapien lacinia ac. Phasellus ullamcorper ante ut lacus dictum commodo.

  Integer sit amet lacus ornare massa feugiat tempus. 



', null, null);`;





const output = input.replace(

  /(["'])([sS]*?)1(?=[,)])/g,

  (_, delim, content) => delim + content.replace(/'/g, "\'") + delim

);

console.log(output);

https://regex101.com/r/RRmq8g/1

If you want to escape "s like that when the delimiters are ", then construct the replacer regex from the delim:

const output = input.replace(

  /(["'])([sS]*?)1(?=[,)])/g,

  (_, delim, content) => {

    const pattern = new RegExp(delim, 'g')

    return delim + content.replace(pattern, '\' + delim) + delim

  }

);

const input = `(1, "22 foo bar "double quotes" here ", '2006-11-10', '3', 'field1', 'field2', '2006-12-12', '11:20:06', '''field3', '



Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus ex ipsum, luctus id dignissim tempor, volutpat eget sapien. Cras accumsan molestie iaculis. Mauris vehicula a nunc vel tincidunt. 

 Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. 



    - Lorem ipsum dolor '' sit amet - ''''



 Aliquam vestibulum tincidunt odio, id varius sapien lacinia ac. Phasellus ullamcorper ante ut lacus dictum commodo.

  Integer sit amet lacus ornare massa feugiat tempus. 



', null, null);`;





const output = input.replace(

  /(["'])([sS]*?)1(?=[,)])/g,

  (_, delim, content) => {

    const pattern = new RegExp(delim, 'g')

    return delim + content.replace(pattern, '\' + delim) + delim

  }

);

console.log(output);

/(["'])([sS]*?)1(?=[,)])/g means:

• 
  (["']) Match and capture a delimiter


• 
  ([sS]*?) - Repeat any character, until you get to:


• 
  1 - The delimiter, followed by


• 
  (?=[,)]) - either a comma, or a )