Regex for validating table name in SQL Server (database compatibility level 120)

Question

I am trying to validate the table/column name using this Stack Overflow answer, but it's not working.

For ValidateTableName("18_18_mapped"), it returns false. But for ValidateTableName("mappingStorage_f9dc07dbca414e2d86db00114a9104a3") - it returns true.

Any input on validating the table/column name would be helpful.

static void Main(string[] args)
{
    bool temp = ValidateTableName("18_18_mapped"); // Returns false
    ...
}

private static bool ValidateTableName(string tableName)
{
    string regexForTableName = @"^[\p{L}_][\p{L}\p{N}@$#_]{0,127}$";
    return Regex.IsMatch("[" + tableName + "]", regexForTableName);
}

for ValidateTableName("18_18_mapped"), it returns false. But for ValidateTableName("mappingStorage_f9dc07dbca414e2d86db00114a9104a3"); returns true — NJMR, Jun 08 '15 at 08:57
Have you tried to use different pattern, such as: `\d+_\d+_mapped`? — Maciej Los, Jun 08 '15 at 09:06
You do not use the answer in http://stackoverflow.com/questions/30151800/regular-expression-for-validating-sql-server-table-name/30152027#30152027 correctly. You add square brackets. — Stefan Steinegger, Jun 08 '15 at 09:34

Wiktor Stribiżew · Accepted Answer · 2015-06-08T09:26:26.643

6

Do not add the square brackets:

return Regex.IsMatch(tableName, regexForTableName);

The brackets in the pattern are necessary to denote a character class. These are not literal square brackets.

Also, I'd declare/compile the regex outside the method for better efficiency:

private static readonly Regex regexForTableName = new Regex(@"^[\p{L}_][\p{L}\p{N}@$#_]{0,127}$");

private static bool ValidateTableName(string tableName)
{
    return regexForTableName.IsMatch(tableName);
}

EDIT:

According to MSDN, these are the specifications for database identifiers. I cannot find any specs stating that the first character can be a digit. However, if you need to allow it, add the \p{N} pattern:

^(?:[\p{N}\p{L}_][\p{L}\p{N}@$#_]{0,127}|\[.{1,126}\])$

I am adding \[.{1,126}\] to support all the names that are enclosed into [], and they must be 128 characters long. The regex matches only non-temporary table names.

edited Jun 08 '15 at 09:26

answered Jun 08 '15 at 08:49

Wiktor Stribiżew

607,720
39
448
563

for ValidateTableName("18_18_mapped"), it returns false even if I remove the brackets. But for ValidateTableName("mappingStorage_f9dc07dbca414e2d86db00114a9104a3"); returns true – NJMR Jun 08 '15 at 08:54
1

`18_18_mapped` starts with a digit and does not meet the regex pattern. Why do you expect it to be true? – Wiktor Stribiżew Jun 08 '15 at 08:58
but 18_18_mapped is a valid sql table name. – NJMR Jun 08 '15 at 08:58
So, it seems you have database compatibility level set to other than 100 value. What is it set to in your case? – Wiktor Stribiżew Jun 08 '15 at 09:03
database compatibility level is at 120 – NJMR Jun 08 '15 at 09:08
Thanks bro... I am new to .Net... thanks for the help. – NJMR Jun 08 '15 at 09:31
second part of expression should be \\[.{1,128}\\] this is because square sql server will use the outer most square brackets as an escape sequence so you can have 128 characters between the outermost square brackets – Jpsh Oct 07 '16 at 13:48
@NJMR I just tested running `CREATE TABLE 18_18_mapped (Foo int)` at compatibility 120 and it fails. Did you mean `[18_18_mapped]`? – Crono Sep 09 '19 at 20:31

Regex for validating table name in SQL Server (database compatibility level 120)

1 Answers1