VB.NET SQL Query Construction Issue When Removing Characters from Phone Numbers

Data and Databases
1

I’m working on a phone number search feature where users can enter numbers in different formats. For example, if someone searches for (555) 123-4567, the database might contain entries like:

  • 5551234567
  • 15551234567
  • 555-123-4567
  • 555 123 4567
  • 555.123.4567

I need to clean both the search input and build a SQL query that strips unwanted characters. Here’s my current VB.NET code:

Dim charactersToStrip() As String = {"+", "-", "(", ")", ".", " ", ","}
Dim searchNumber As String = txtPhoneInput.Text.Trim()
Dim sqlColumn As String = "ContactPhone"
For Each character As String In charactersToStrip
    searchNumber = searchNumber.Replace(character, "")
    sqlColumn = String.Format("REPLACE({0}, '{1}', ''))", sqlColumn, character)
Next
If Not (String.IsNullOrEmpty(searchNumber)) Then
    If (String.IsNullOrEmpty(queryString)) Then
        queryString = String.Format(" {0} LIKE '{1}'", sqlColumn, searchNumber)
    Else
        queryString = String.Format("{0} AND {1} LIKE '{2}'", queryString, sqlColumn, searchNumber)
    End If
End If

The problem happens when building the nested REPLACE functions in this line:

sqlColumn = String.Format("REPLACE({0}, '{1}', ''))", sqlColumn, character)

I get an error saying “Input string was not in a correct format.” What’s wrong with my string formatting approach?

2

You’ve got an extra closing parenthesis in your String.Format call. You’re using REPLACE({0}, '{1}', ')) when it should be REPLACE({0}, '{1}', ''). That extra parenthesis breaks the SQL syntax and messes up the string formatter. I ran into this same issue when building dynamic address queries. Drop that extra parenthesis and your nested REPLACE functions will work fine. Also, you should use parameterized queries instead of string concatenation - it’ll protect you from SQL injection since you’re putting user input straight into the query.

3

i think that extra parenthesis could be causing the issue! maybe try simplifying it or using a stored procedure for replacement? that might make it cleaner, too! what formats are you expecting users to search with?

4

you’ve got an extra closing paren in your replace statement. should be REPLACE({0}, '{1}', '') instead of REPLACE({0}, '{1}', ')). that’s what’s breaking your string format method.