Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

regex - Regular expression for validating SQL Server table name

I am creating dynamic SQL Server table using C# code but I need to validate the table name.

What is a regular expression for validating SQL Server table names?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The regex described in the link should be:

var regex = new Regex(@"^[p{L}_][p{L}p{N}@$#_]{0,127}$");

Note that in general you'll have to embed the name of the table in [...], because of the rule 3 (so SELECT * FROM [SET] is a valid query, because, while SET is a reserved keyword, you can "escape" it with the [...])

Note that in the linked page the rule is incomplete:

From https://msdn.microsoft.com/en-us/library/ms175874.aspx

  1. The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words. When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets. The words that are reserved depend on the database compatibility level. This level can be set by using the ALTER DATABASE statement.

And they forgot: https://msdn.microsoft.com/en-us/library/ms174979.aspx

Is the name of the new table. Table names must follow the rules for identifiers. table_name can be a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

The rule that I've written is for "full" tables, not for temporary tables, and doesn't include schema name.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...