I am writing some code that parses some SQL expressions, but have run into a problem I cannot solve on my own. I want to replace a certain word in a SQL statement but not if that word is contained within a phrase that is enclosed in single or double quotes. My use case is SQL expression parsing but I think this is more a generic string replacement thing.

To take an example consider the expression: select {foo} from Table where {foo} = "This is {foo} today" or {foo} = 'this is {foo} tomorrrow' or {foo} = "it's all '{foo}' to me!"

Assuming I want to replace {foo} to the string bar, the output would be: select bar from Table where bar = "This is {foo} today" or bar = 'this is {foo} tomorrow' or bar = "it's all '{foo}' to me!"

As we can see all {foo} expressions enclosed within quotes (single or double) have not been replaced.

We can make the assumption that quotes will be closed, i.e. there will be no stray quotes floating around (where {foo} = 'un'even" is not a use case we need to consider.)

Text within nested quotes should not be replaced (as however you look at it the text is contained within quotes :) ) The example shows an example of this in the or {foo} = "it's all '{foo}' to me!" part (as well as containing three single quotes just for fun)

I have done quite a bit of research on this and it seems a tricky thing to do in Javascript (or any other language no doubt). This seems a good fit for regex, but any javascript solution regex or not would be helpful. The closest I have come in Stack Overflow to a solution is Don't replace regex if it is enclosed by a character but it isn't a close enough match to help

CodePudding user response：

You could do a regex replacement on '. ?'|\{foo\}, using a callback, and only make a replacement in the latter match. Note that string literals in SQL use single quotes, not double quotes. I will therefore refactor your query to use only single quotes for strings.

var sql = "select {foo} from Table where {foo} = 'This is {foo} today' or {foo} = 'this is {foo} tomorrrow'  or {foo} = 'it''s all ''{foo}'' to me!'";
var output = sql.replace(/'. ?'|\{foo\}/g, (x) => x.match(/^\{.*\}$/) ? "bar" : x);
console.log(output);

Notes:

• The regex pattern tries to match terms in single quotes first, which ensures that we only match {foo} not in a string literal.
• A literal single quote in SQL is formed by doubling them up ''

CodePudding user response：

I doubt it could be done with a unique regular expression directly on your specific example since it's possible to have single quotes alone, interlaced single or double quotes, conflict of precedence etc. The best way according to me is to code a simple naive solution that works for most of your very use cases and then see if there's rooom for optimisation.

Something like the following in Javascript is working even if not really optimal (e.g. a replace with a callback could be less readible but morce concise):

const source = `select {foo} from Table where {foo} = "This is {foo} today" or {foo} = 'this is {foo} tomorrow'  or {foo} = "it's all '{foo}' to me!"`;
const solution = `select bar from Table where bar = "This is {foo} today" or bar = 'this is {foo} tomorrow'  or bar = "it's all '{foo}' to me!"`;

const exclusions = [];
const exclusionPatterns = [/\".*?\"/g, /\'.*?\'/g];
let modifiedSource = source;

for (const pattern of exclusionPatterns) {
  let matches;
  while (true) {
    matches = modifiedSource.match(pattern);
    if (!matches) {
      break;
    }
    const firstMatch = matches[0];
    modifiedSource = modifiedSource.replace(
      firstMatch,
      `$${exclusions.length}`
    );
    exclusions.push(firstMatch);
  }
}

const destination = modifiedSource.replace(/(\{foo\}) /g, "bar");
let modifiedDestination = destination;

for (const [index, exclusion] of exclusions.entries()) {
  modifiedDestination = modifiedDestination.replace(`$${index}`, exclusion);
}

console.log(source);
console.log(modifiedSource);
console.log(modifiedDestination);
console.log(solution);