Parsing of string for SQL DataType

Question

I'm doing some reflection on generated LINQ to SQL classes (i.e. DataContext). For each property (column) on a class (table) I'm getting the ColumnAttribute.DbType value.

It can be various different values, here's some examples:

• Int NOT NULL
• Int
• VarChar(255)
• VarChar(255) NOT NULL
• Bit

... and so on.

If I wanted to parse these strings into seperate meaningful values, such as "DataType", "Nullable" and "MaxLength", what would the best approach be?

I don't need a full-on lexer/parser thingy or language interpreter, I don't think it's necessary to go after something like M. Other than some (horrible) specific manual parsing involving Substring, what would you recommend?

Answers in C#, please - that's pretty much all I'm good for these days :)

Answer 1

You could to use a regular expression to match those patterns:

string input = @"
    Int NOT NULL
    Int
    VarChar(255)
    VarChar(255) NOT NULL
    Bit";
foreach (Match m in Regex.Matches(input,
    @"^\s*(?<datatype>\w+)(?:\((?<length>\d+)\))?(?<nullable> (?:NOT )?NULL)?",
    RegexOptions.ECMAScript | RegexOptions.Multiline))
{
    Console.WriteLine(m.Groups[0].Value.Trim());
    Console.WriteLine("\tdatatype: {0}", m.Groups["datatype"].Value);
    Console.WriteLine("\tlength  : {0}", m.Groups["length"  ].Value);
    Console.WriteLine("\tnullable: {0}", m.Groups["nullable"].Value);
}

Answer 2

Would this do, using regexp as shown below:

public Regex MyRegex = new Regex(@
      "(?<datatype>([tiny|big]?int|long)?|(?<varchar>varchar)?)\s?"+
      "(\((?<len>\d{1,})\))?\s?(?<nullable>(?<not>not)?\s+(?<null>null))?",
    RegexOptions.IgnoreCase
    | RegexOptions.CultureInvariant
    | RegexOptions.IgnorePatternWhitespace
    | RegexOptions.Compiled
    );

Match m = MyRegex.Match(InputText);

// Example invocation...
if (m.Success){
  string DataType = m.Groups["datatype"].Value;
  int DataLen = m.Groups["len"].Value;
  string Nullable = m.Groups["nullable"].Value;
}

For the example above, it matches:

int null
varchar(255) not null

Also the regexp only matches tinyint, bigint, int, long and varchar as an example to get you started.

Hope this helps, Best regards, Tom.