Sanjiv Sanjiv - 2 months ago 13
C# Question

Regular Expression to get a string between backtick `` in Console application (C#)

I am trying to parse SQL by using regular expression in order to return aliases (a string after keyword

AS
between backtick ``). For instance for the input

var test = "select t.`ProductID` AS `ProductID`, t.`AttributeID` AS `AttributeID`, t.`String_Value` AS `String_Value`, t.`Numeric_Value` AS `Numeric_Value`, t.`MetaData` AS `MetaData`, t.`DataType` AS `DataType`, t.`CreatedTime` AS `CreatedTime`, t.`CreatedBy` AS `CreatedBy`, t.`ModifiedTime` AS `ModifiedTime`, t.`ModifiedBy` from dfsfile.tmp1.my_json t";


the expected return is

"ProductID",
"AttributeID",
"String_Value",
"Numeric_Value",
"MetaData",
"DataType",
"CreatedTime",
"CreatedBy",
"ModifiedTime"

Answer

Something like this (Regular expression and Linq):

 String test = "select t.`ProductID` AS `ProductID`, t.`AttributeID` ...";

 // If you want to preserve `` the pattern is @"\bAS\s*(`[^`]*?`)"
 String pattern = @"\bAS\s*`([^`]*?)`";     

 var result = Regex
    .Matches(test, pattern, RegexOptions.IgnoreCase)
    .OfType<Match>()
    .Select(match => match.Groups[1].Value)
    .ToArray(); // if you want, say, an array representation

 Console.Write(String.Join(", ", result));

And you'll get

  ProductID, AttributeID, ... , ModifiedBy

However, be careful: in general case regular expressions are not a good choice for parsing SQL; let me provide some examples to show the problems emerging:

  -- commented AS ("abc" should not be returned)
  select a /* AS `abc`*/
    from tbl 

  -- commented value ("abc" should be returned, not "obsolete" or "proposed")
  select a AS /*`obsolete`*/ `abc` /*`proposed`*/
    from tbl 

  -- String ("abc" should not be returned)
  select 'a AS `abc`'
    from tbl

  -- honest AS ("abc" should be returned)
  select a /*'*/AS `abc`--'
    from tbl

  -- commented comment ("abc" should be returned)
  select -- /*
        a AS `abc`
        --*/
   from tbl   
Comments