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 :)

Rubens Farias · Accepted Answer · 2010-01-29 00:41:40Z

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);
}

answered Jan 29, 2010 at 0:41

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

t0mm13b · Accepted Answer · 2010-01-29 01:06:22Z

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.

Collectives™ on Stack Overflow

Parsing of string for SQL DataType

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related