Uppercase the keywords in a string in C# (SQL Beautification with keywords)?

Question

I have some keywords

private static readonly string[] Keywords =
{
  "SELECT", "FROM", "WHERE", "GROUP", "HAVING", "ORDER", "LEFT", "RIGHT", JOIN", "INNER", "OUTER", "ASC", "DESC", "AND", "OR","IN", "BETWEEN", "BY", "NOT", "ON", "AS", "CASE", "WHEN", "ELSE"
};

I want to uppercase these keywords in a string

  string Query = "Select * fRom TableA"

This string should be converted to SELECT * FROM TableA

That is, Case will be ignored and keywords will be uppercased.

For this, I have written a function

    public static string GetUppercaseKeyword(string sqlStr)
    {
        string sqlText = string.Empty;
        int foundAt = 0;

        foreach (var keyword in Keywords)
        {
            if (sqlStr.IndexOf(keyword, foundAt, StringComparison.CurrentCultureIgnoreCase) == -1)
            {
                continue;
            }

            sqlStr = Replace(sqlStr, keyword, keyword.ToUpper(), StringComparison.OrdinalIgnoreCase);
        }

        return sqlStr;
    }

and the replace function is

  public static string Replace(string str, string old, string @new, StringComparison comparison)
    {
        @new = @new ?? "";
        if (string.IsNullOrEmpty(str) || string.IsNullOrEmpty(old))
            return str;
        int foundAt = 0;
        while ((foundAt = str.IndexOf(old, foundAt, StringComparison.CurrentCultureIgnoreCase)) != -1)
        {
            str = str.Remove(foundAt, old.Length).Insert(foundAt, @new);
            foundAt += @new.Length;
        }
        return str;
    }

The thing that happens here is, if a substring matches with the keywords, the method uppercase the substrings, as in Replace method I use IndexOf

For example, select * From OrderTable will output SELECT * FROM ORDERTable.

How can I solve the problem?

My other questions are, is there any easy way to implement it? Is there any existing algorithm for this type of purposes?

This is going to be really tricky as you will have to figure out word boundaries, which don't always mean white space (think of periods, exclamation marks, question marks, etc. — rory.ap, Jan 25 '17 at 13:58
@rory regular expressions can detect word boundaries just fine. — CodeCaster, Jan 25 '17 at 13:59
_"is there any easy way to implement it?"_ - what, parsing SQL and pretty-printing it? Yes, other people have done that long before you, you do **not** want to reinvent that wheel. Good luck processing `insert into [Group] ([From], [As], [On]) values ('select', 'table', 'in -- order')` that's going to be passed onto a case-sensitive database engine. Abandon this ship while you still can. Do it _well_ or don't do it at all. — CodeCaster, Jan 25 '17 at 14:01
@CodeCaster : Yes, I just wanted to implement a SQL Query beautifier for my learning. Can you please mention some of the existing algorithms to do this type of parsing. — Noor A Shuvo, Jan 25 '17 at 14:10

score 1 · Answer 1 · answered Jan 25 '17 at 14:04

Here is a way.

void Main()
{

    Console.WriteLine(Replace());

}

private static readonly string[] Keywords =
{
  "SELECT", "FROM", "WHERE", 
  "GROUP", "HAVING", "ORDER", 
  "LEFT", "RIGHT", "JOIN", "INNER", 
  "OUTER", "ASC", "DESC", "AND", "OR","IN", 
  "BETWEEN", "BY", "NOT", "ON", "AS", "CASE", "WHEN", "ELSE"
};

static string Query = "Select * fRom TableA";

static bool Exists(string word) =>
    Keywords.Any(x => x.Equals(word, StringComparison.OrdinalIgnoreCase));


static string Replace()
{
    var parts = Query.Split(' ');

    var upperedParts = parts.Select(x => (Exists(x)) ? x.ToUpper() : x);

    return String.Join(" ", upperedParts);

}

// Define other methods and classes here

`select[foo],[bar]from[table]` is valid SQL. The space separator (`Query.Split(' ')`) is too naive for a proper implementation. Keywords can also occur as column names or aliases, which you _don't_ want to replace. — CodeCaster, Jan 25 '17 at 14:12

Uppercase the keywords in a string in C# (SQL Beautification with keywords)?

1 Answers1