在MS Access SQL中如何将电话号码格式化为(###) ###-####？

Looks like your original SQL logic is way off track here—let's fix that step by step. The issue is that your Left() and Mid() parameters aren't targeting the right parts of the phone number, and you aren't properly splitting the digits into the (###) ###-#### format.

First, let's cover the clean scenario: 10-digit pure numbers #

If your Value field already contains 10-digit numeric strings (like "1239871234"), this simple UPDATE statement will format it correctly:

UPDATE Table 
SET Value = "(" & Left(Value, 3) & ") " & Mid(Value, 4, 3) & "-" & Mid(Value, 7, 4)
WHERE Field = "Phone"

But your existing data has partial formatting (like "(123) 9871234") #

Since your current numbers already have parentheses and spaces, we need to first strip out all non-numeric characters, then apply the format. Access SQL doesn't have a built-in "remove non-digits" function, so we have two options:

Option 1: Nested Replace functions (no VBA needed)

We'll chain Replace() to get rid of parentheses and spaces before formatting:

UPDATE Table 
SET Value = 
    "(" & Left(Replace(Replace(Replace(Value, "(", ""), ")", ""), " ", ""), 3) & ") " & 
    Mid(Replace(Replace(Replace(Value, "(", ""), ")", ""), " ", ""), 4, 3) & "-" & 
    Mid(Replace(Replace(Replace(Value, "(", ""), ")", ""), " ", ""), 7, 4)
WHERE Field = "Phone"

Option 2: Custom VBA function (cleaner for messy data)

If you have other non-digit characters (like hyphens) in some entries, create a quick VBA function to extract only digits:

1. Open your Access database, press Alt + F11 to open the VBA editor.
2. Insert a new module, then paste this code:

Function GetDigits(strInput As String) As String
    Dim strOutput As String
    Dim i As Integer
    For i = 1 To Len(strInput)
        If IsNumeric(Mid(strInput, i, 1)) Then
            strOutput = strOutput & Mid(strInput, i, 1)
        End If
    Next i
    GetDigits = strOutput
End Function

3. Save the module (name it something like modStringUtils).

Now your SQL becomes much cleaner:

UPDATE Table 
SET Value = "(" & Left(GetDigits(Value), 3) & ") " & Mid(GetDigits(Value), 4, 3) & "-" & Mid(GetDigits(Value), 7, 4)
WHERE Field = "Phone"

Let's test this with your example #

Your original value: (123) 9871234

• After extracting digits: 1239871234
• Formatted result: (123) 987-1234

That's exactly the format you're aiming for.

内容的提问来源于stack exchange，提问作者Jaskier