Detect Invalid Characters in Column Names

Question

Detect Invalid Characters in Column Names

ganeshmuthuvelu

SSCrazy

Points: 2177
More actions
December 21, 2006 at 4:51 pm

#368586

Hello,
In our application, we create tables at run time and the user provides the column names, data types. I need some kind of validation query for the column name so that it does not contain spaces, special characters, dashes; should not begin with numbers, or anything that SQL Server will not like excluding key words since I think I can overcome that problem with square brackets..
@ColName like '[0-9]%' or @ColName like '%-%' or @Colname like .....
Is that the way I have to do my checks??. or Is there any other simpler way?. or even a complex function with all possible checks that somebody has written to validate such names..
Any help will be appreciated.
Thanks,
Ganesh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

There is no simple way to validate...

It is OK to validate the name of the column...but first read the "Rules for Regular Identifiers" in BOL to validate the names...

Rules for Regular Identifiers

http://msdn2.microsoft.com/en-us/library/ms175874.aspx

MohammedU
Microsoft SQL Server MVP

Dylan Finney SSC Enthusiast Points: 164 More actions · Answer 2

Am I missing something? Wouldn't it be easier to use a regular expression at the application level to parse the user input?

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Read my article: Practical Uses of PatIndex: http://www.sqlservercentral.com/columnists/rdavis/practicalusesofpatindex.asp

You could use the sample code there with some slight modifications to get what you need. Use the NOT operator ( ^ ) for pattern matching to easily find if anything doesn't match your specifications. For example:

Declare

@val varchar(128)

Set

@val = 'i35iC4io'

Select

PatIndex('%[^0-9a-z[_]]%', @val)

The above is looking for any characters that do not match 0-9 or a-z or the underscore.

Dylan, you should never rely solely on the validation of the client applications.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 4

Robert,

Thanks for the reply and code piece. However, PatIndex returns 0 even for this..

Declare @val varchar(128)

Set @val = 'i&&35iC4io'

Select PatIndex('%[^0-9a-z[_]]%', @val)

Can you help?.

Thanks,

Ganesh

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

Sorry, I forgot that I don't need to escape the underscore with square brackets because it is already inside of square brackets.

Declare @val varchar(128)

Set @val = 'i&&35iC4io'

Select PatIndex('%[^0-9a-z_]%', @val)

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 6

Thank you Robert!

Merry Christmas and Happy Holidays to all!

-Ganesh

MohammedU SSCoach Points: 19644 More actions · Answer 7

Ganesh,

I believe identifier name should not start with a number...

MohammedU
Microsoft SQL Server MVP