Replace all commas outside parentheses AND quotes with REGEX or/and C#

Solution 1:

You can use

Regex.Replace(text, @"(\([^()]*\)|'[^']*')|\s*,", m => 
    m.Groups[1].Success ? m.Value : " DESC,")

Details:

• ( - Group 1 start (the capturing group is necessary to restore the match later in the resulting string):
  • \([^()]*\) - a ( char, then any zero or more chars other than ( and ) and then a ) char
• | - or
  • '[^']*' - ', zero or more chars other than ', and a ' char
• ) - end of the capturing group
• | - or
• \s*, - zero or more whitespaces and then a , char.

See the C# demo:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

public class Test
{
    public static void Main()
    {
        var text = "ISNULL(d.Type, 0), d.Subject + ', ' + d.Name, d.Something,array_position(ARRAY['f', 'p', 'i', 'a']::varchar[], x_field), test";
        var pattern = @"(\([^()]*\)|'[^']*')|\s*,";
        var result = Regex.Replace(text, pattern, m => m.Groups[1].Success ? m.Value : " DESC,");
        Console.WriteLine(result);
    }
}

Output:

ISNULL(d.Type, 0) DESC, d.Subject + ', ' + d.Name DESC, d.Something DESC,array_position(ARRAY['f', 'p', 'i', 'a']::varchar[], x_field) DESC, test

Solution 2:

I think I'd just have a simple state machine style interpretation..

bool inBra = false;
bool inQuo = false;

var sb = new StringBuilder();

foreach(char c in str){
  if(c == '(')
    inBra = true;

  if(c == ')')
    inBra = false;

  if(c == '\'')
    inQuo = !inQuo;

  if(c == ',' && !inBra && !inQuo)
    sb.Append(" DESC,");
  else
    sb.Append(c);
}

You could handle nesting by making the bool an int that you ++ and --, and only DESC when it's 0

Solution 3:

I doubt if Regex is a good tool for the task: what if you have parentheses within quote?

abc + '(' + def, pqr + ')'

you want a simple, but parser. Let's implement it as a Finite State Machine:

private static string MyReplace(string value) {
  if (string.IsNullOrEmpty(value))
    return value;

  StringBuilder sb = new StringBuilder(value.Length * 2);

  // if we are within quotation
  bool inQuot = false;
  // if we are within parenthesis (note, they can be nested)
  int parenthesis = 0;

  foreach (char c in value) {
    if (c == ',' && !inQuot && parenthesis == 0) 
      sb.Append(" DESC,");
    else
      sb.Append(c);

    // Possible states changing
    if (c == '\'')
      inQuot = !inQuot;
    if (!inQuot && c == '(')
      parenthesis += 1;
    if (!inQuot && c == ')')
      parenthesis -= 1;
  }

  return sb.ToString();
}