Phone number conversion

[dmaranan]

Does anyone know how to convert a telephone number in the following format (314)785-1234 to 3147851234 in an SQL query? I tried the format function, but to know avail.

 

[nicsin]

save this in a module

Public Function Replace(strSource As String, String1 As String, String2 As String) As String
    Dim iLoc As Integer
    Dim NewString As String
    NewString = strSource
    iLoc = InStr(NewString, String1)
    Do While iLoc > 0
        NewString = Left$(NewString, iLoc - 1) & String2 & Mid$(NewString, iLoc + 1)
        iLoc = InStr(iLoc + 2, NewString, String1)
    Loop
    Replace = NewString
    
End Function

an the query:

SELECT replace(replace(replace([phoneNo],"-",""),")",""),"(","") AS replaced
FROM myTable;

 

[dmaranan]

Figured it out.

SELECT (mid(DataStuComm.Number, 2,3) & mid(DataStuComm.Number, 7,3) & mid(DataStuComm.Number, 11,4) ) AS newphone
FROM Comm

 

[bosk00]

You can also use to MID$ and Right$ to extract the number.

Val(Mid$([TableName].[Phone Number],2,3)&Mid$([TableName].[Phone Number],6,3)&Right$([TableName].[Phone Number],4)) AS [New Number]

 

[LNBruno]

And REPLACE is a built in function; you don't have to write your own! For example:

sFixed = Replace(sToFix, "'", "''")

< M!ke >