使用C#和正则表达式从SQL select语句中删除别名 [英] Removing aliases from a SQL select statement, using C# and regular expressions

问题描述

我正在学习正则表达式，并且正在与它们一起玩一些.我为自己提出了一个练习，其中有一个方法可以删除SQL Select语句中的列别名.这应该是这样的:

I’m learning regular expressions and I’m playing a little with them. I proposed myself an exercise in which I have a method that removes the columns aliases in a SQL Select statement. This should work like this:

• 该方法可以删除SQL中的别名使用AS关键字的select语句:选择ColumnA AS A"
• 该方法可以删除SQL中的别名没有AS的select语句关键字:选择ColumnB B"
• 该方法可以删除SQL中的别名选择包含以下内容的语句操作字符"(例如串联运算符):选择'Hello'||世界！"HelloWorld"

到目前为止，我已经创建了两种仅适用于特定情况的方法.以下代码总结了我所做的事情以及所面临的问题.

So far I have created two methods that only work on specific cases. The following code provides a summary of what I’ve done and about the problems that I’m facing.

static void Main(string[] args)
{
    string cols1 = "ColA as AliasA, ColB   AliasB  , As As ASasas, Asasasas as As";
    string cols2 = "'aaa' || 'bbb'  AS   AliasC , 'ccc' || 'ddd' AliasD";

    string answer1 = RemAliases(cols1);     // Works fine
    string answer2 = RemAliases2(cols2);    // Works fine
    string answer3 = RemAliases2(cols1);    // Doesn't work
    string answer4 = RemAliases(cols2);     // Doesn't work            
}

static string RemAliases2(string inputSql)
{
    string pattern1 = @"(.+)\s+AS\s+\w+";
    string replacement1 = "$1";
    string pattern2 = @"(.+)\s+\w+";
    string replacement2 = "$1";
    string result = Regex.Replace(inputSql, pattern1, replacement1, RegexOptions.IgnoreCase);
    result = Regex.Replace(result, pattern2, replacement2, RegexOptions.IgnoreCase);
    return result;
}

static string RemAliases(string inputSql)
{
    string pattern1 = @"(\w+)\s+AS\s+\w+";
    string replacement1 = "$1";
    string pattern2 = @"(\w+)\s+\w+";
    string replacement2 = "$1";
    string result = Regex.Replace(inputSql, pattern1, replacement1, RegexOptions.IgnoreCase);
    result = Regex.Replace(result, pattern2, replacement2, RegexOptions.IgnoreCase);
    return result;
}

我不希望"RemAliases()"与"cols2"一起正常工作，因为"\ w +"与"|"不匹配特点.虽然，我期望"RemAliases2()"与"cols1"也能正常工作.有人可以帮我一些忙，以了解为什么"RemAliases2()"在"cols1"情况下无法正常工作吗?请随时提供有关我使用这些正则表达式的方式的其他建议.

I wasn’t expecting "RemAliases()" to work fine with "cols2", because the "\w+" doesn’t match the "|" character. Although, I was expecting "RemAliases2()" to also work fine with "cols1". Can someone please provide me some help in order to know why "RemAliases2()" doesn’t work fine for the "cols1" case? Please, feel free to provide any other kind of suggestions about the way I’ve used these regular expressions.

谢谢.

PS:我正在使用.NET 2.0

PS: I’m using .NET 2.0

推荐答案

非正则表达式方法:

/// <summary>
/// Remove SQL aliases from a string of selects
/// </summary>
/// <param name="select">A string of selects</param>
/// <returns>A string of selects without any aliases</returns>
public static string RemoveAliases(string select)
{
  string[] originalSelect = select.Split(',');
  string[] newSelect = (string[])originalSelect.Clone();
  string alias = " as ";
  for (int i = 0; i < originalSelect.Length; i++)
  {
    int aliasIndex = originalSelect[i].IndexOf(alias, StringComparison.InvariantCultureIgnoreCase);
    if (aliasIndex >= 0)
    {
      string withoutAlias = originalSelect[i].Substring(0, aliasIndex);
      newSelect[i] = withoutAlias;
    }
  }

  StringBuilder sbNoAliases = new StringBuilder();
  for (int i = 0; i < newSelect.Length - 1; i++)
  {
    sbNoAliases.Append(newSelect[i] + ",");
  }
  sbNoAliases.Append(newSelect[newSelect.Length - 1]);

  return sbNoAliases.ToString();
}

这篇关于使用C#和正则表达式从SQL select语句中删除别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！