CODEWITHSUNDEEP
regexvb.netdelimiter
Kai
Kai

Reputation: 2168

Regex (or similar approach) to match SQL Server field name that should be delimited

I want to write a regex in VB.NET that takes a string and determines whether it should be delimited for it to be valid as a field name in SQL server.

For example, myField is valid (example context: SELECT myField from myTable), but my field is not and would need to be delimited with square brackets (select [my field] from myTable).

The regex should match a string containing any of the following:

My current regex pattern is [^A-Za-z]+, which thereabouts works as it matches any non-alphabetical character, but it unecessarily matches names such as my_field or field0

Regex answer is preferred, but other approaches are fine too

Upvotes: 0

Views: 226

Answers (2)

Bohemian
Bohemian

Reputation: 425258

This regex matches your criteria:

^([\d@]|.*[^\w@#$]).*$

...and more! Rather than having to specify all invalud characters (there are 64K of them) this uses negation of characters that are valid.

There are really only two types of problem:

  • the initial char
  • the remaining chars

Whitespace does not need a special check - they are just done if the thousands of non-valid characters.

Because you want a regex that matches what's invalid, the two types of problem are ORed.

Upvotes: 0

user2480047
user2480047

Reputation:

Here you have a non-Regex-based solution.

Dim inputString As String = "my field"
If (fieldIsValid(inputString)) Then
    'Do anything
End If

Where fieldIsValid is a function defined by:

Private Function fieldIsValid(inputString As String) As Boolean
    Dim delimiters() As String = New String() {" ", "!", """", "£", "$", "%", "^"}
    If StringContains(inputString, delimiters) Then
        Return False
    End If

    If (IsNumeric(inputString.Substring(0, 1)) OrElse inputString.Substring(0, 1) = "@") Then 
        Return False
    End If

    Return True

End Function

Private Function StringContains(ByVal inputString As String, ByVal ParamArray delimiters() As String) As Boolean
    For Each delimiter In delimiters
        If inputString.Contains(delimiter) Then
            Return True
        End If
    Next

    Return False
End Function

As you can see, the code-size difference with respect to a Regex-based solution is not too big this time; also this kind of solutions provide more flexibility if you want to improve/extend it in the future. Regarding "special characters" you would have to be more specific; even in case of using Regex, you would have to tell what you consider a special character.

Upvotes: 1

Related Questions