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

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 10 years ago.

The community reviewed whether to reopen this question 3 years ago and left it closed:

Original close reason(s) were not resolved

Improve this 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
– NJMR, Commented Jun 8, 2015 at 8:57
Have you tried to use different pattern, such as: \d+_\d+_mapped? — Maciej Los
– Maciej Los, Commented Jun 8, 2015 at 9:06
You do not use the answer in stackoverflow.com/questions/30151800/… correctly. You add square brackets. — Stefan Steinegger
– Stefan Steinegger, Commented Jun 8, 2015 at 9:34

Wiktor Stribiżew · Accepted Answer · 2015-06-08 09:26:26Z

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 8, 2015 at 9:26

answered Jun 8, 2015 at 8:49

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

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

7 Comments

NJMR Over a year ago

for ValidateTableName("18_18_mapped"), it returns false even if I remove the brackets. But for ValidateTableName("mappingStorage_f9dc07dbca414e2d86db00114a9104a3"); returns true

Wiktor Stribiżew Over a year ago

18_18_mapped starts with a digit and does not meet the regex pattern. Why do you expect it to be true?

NJMR Over a year ago

but 18_18_mapped is a valid sql table name.

Wiktor Stribiżew Over a year ago

So, it seems you have database compatibility level set to other than 100 value. What is it set to in your case?

NJMR Over a year ago

database compatibility level is at 120

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related