Stripping spaces

[JDubya]

Hi All, I have a table - tblCustomers that contains the text field c_PhoneNumber that surprise surprise holds phone numbers. The trouble is that many of the records contain non-numeric characters such as hyphens and brackets as well as spaces. I would like to strip all of these out with an SQL query or queries if need be or if that's not possible then a bit of VBA code. Any ideas? Help me Obi Wan, you're my only hope!

Cheers
John

 

[jdraw]

What do you consider valid characters for a phone number?

With a function you can parse current contents and ensure each character is in your valid character list.

 

[JDubya]

Hiya, I only want numeric characters, so 0 to 9 and I want there to be no spaces. The trouble is I have values such as "0208 941 5998" or "(0207) 996 2255" or "01465-279-221" or variations on these. Because I import data at various times, I would like to automate the process whereby a query when run would strip out all these undesirable characters and leave each record all numeric, so 02089415998 for example. Plus it's been a long time since I've delved into database stuff, so I'm afraid I don't even know what you mean by "parsed".

 

[jdraw]

Here is a function to do what you are asking.

'---------------------------------------------------------------------------------------
' Procedure : fExtractStrt
' DateTime  : 12/10/2004 22:47
' Author    : mellon
' Purpose   : Return the Numeric parts of the input string
'   This ignores non numeric characters and returns only the
'   numeric string in the order it was input.
'
' Sample call:  
'
'Sub testForNumeric()
'
'Dim x As String: x = "123 4567  8966"
'Debug.Print fExtractStrt(x)

'''''gives result: 12345678966
'End Sub

'---------------------------------------------------------------------------------------
'Reviewed Dec 2004
Function fExtractStrt(strInString As String) As String
'JED Jan 2000
'This routine can take a combined Areacode and phone/fax number
'    (234) 234-4567 in various formats
'and output 10 digits contiguous
'where left 3 is Area code
'     right 7 is number
'
'
'It could be used on any string to remove " ",(,), or - characters
'
' chr 32 space
' chr 40 (
' chr 41 )
' chr 45 -
' chr 46 .

'************ Code Start **********

Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String

    lngLen = Len(strInString)
    strOut = ""
    
    If lngLen = 0 Then GoTo NullWasSupplied_Exit
    
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        
        If Asc(strTmp) = 32 Or _
            Asc(strTmp) = 40 Or _
            Asc(strTmp) = 41 Or _
            Asc(strTmp) = 45 Or _
            Asc(strTmp) = 46 Then
            GoTo bypassOutput
        Else
            strOut = strOut & strTmp
        End If
bypassOutput:
    Next i
     
NullWasSupplied_Exit:

    fExtractStrt = strOut
    
End Function

Create a new standard module, then copy the function into the module.
Save the module and function.

To use the function:

fExtractStrt(c_PhoneNumber)

in a Select query on your table, select the field

c_PhoneNumber and X: fExtractStrt(c_PhoneNumber)

This will show you the current (c_PhoneNumber) values and the "cleaned" values.

Try it and let us know how you do.

Then we'll move on to updating the values in the table.

 

[JDubya]

Many thanks for your help with this jdraw, I'll have a crack at it and let you know how I get on. Cheers, John

 

[JDubya]

Hi jdraw

I don't quite understand how it works, but it works a treat!! So, how do I now update the values?

 

[CJ_London]

another way of looking at it, rather than excluding characters you don't want, only include characters you do want

something like

 public function findNumber(str as string) as string
 dim I as integer
 dim tstr as string
  
     'asc("0")=48
     'asc("9")=57
  
     tstr=""
     if len(str)>0 then
         for I=1 to len(str)
             if asc(mid(str,i,1))>=48 and asc(mid(str,i,1))<=57 then tstr=tstr & mid(str,i,1)
         next I
     end if
  
     findnumber=tstr
  
 end function

Note, for telephone numbers, it is customary to retain the preceding 0 so your revised number does still need to be stored as a string. You may also need to think about international numbers

 

[jdraw]

JDubya,

How it works:

The function takes the value of the parameter.
It checks character by character until finished all characters.
If the character is a number/digit, then it moves that character to output, and goes to check the next character.
If the character is a character to ignore/drop, then it does not output the character, and goes to check the next character.
When all characters have been reviewed, it returns the outputted string.

Updating:

An update query can be very unforgiving. That is, it's possible to screw things up - quickly.

Make a copy of your original table, and save it as Orig_yourTableName. That gives you a backup, just in case.

Now using the query wizard, create a query using your table. Make it an UPDATE query.
Add the field c_PhoneNumber (select it)
Then in the row that says Update To, under the c_PhoneNumber, put in the function fExtractStrt(c_PhoneNumber) (see sample attached)

Then, run the query. Check your table --if the values are incorrect--let us know exactly what you've got.

 

[JDubya]

Thanks CJ_London

 

[JDubya]

Hi jdraw thank you soooo much for your expert help, you've saved me a helluva lot of time and frustration. I did read one of the existing posts on a similar problem that suggested using the REPLACE function, but I looked at the Access help on the topic and couldn't make much sense of it. Out of interest, would it have worked for me and if so and if you do not feel that I've taken up too much of your time already, could you show me how I would have used it in the query with the c_PhoneNumber field to eliminate a couple of the unwanted type of characters, say the hyphens and spaces? But no worries if you don't want to explain this to me as you've already solved the problem at hand. Cheers, John

 

[jdraw]

No problem.

You could use Replace(c_PhoneNumber,"(","") to remove a left bracket with a zero length string*,
or Replace(c_PhoneNumber," ","") to remove space etc.

zero length string* is a construct that is a string data type but has zero length.

Using Replace(c_PhoneNumber,"(","") the Replace function looks at the string/text (c_PhoneNumber) and then if it finds a ( , it replaces it with the zero length string. In effect, it removes the (.

For more info on Replace

 

[JDubya]

Thanks jdraw....Ok, 3 more I hope brief questions. 1. Would I place the expression Replace(c_PhoneNumber,"(","") in the Update To part of the Update query like I did with the fExtractStrt(c_PhoneNumber) expression/ 2. Do I have to use separate REPLACE expressions to remove each different unwanted type of character example or can it be done with one REPLACE statement 3. and if so then for an example could you modify your first REPLACE statement so that it eliminates hyphens as well as left brackets. Once again, many thanks jdraw.

 

[jdraw]

1. Would I place the expression Replace(c_PhoneNumber,"(","") in the Update To part of the Update query like I did with the fExtractStrt(c_PhoneNumber) expression/ YES

2. Do I have to use separate REPLACE expressions to remove each different unwanted type of character example or can it be done with one REPLACE statement YES

3. and if so then for an example could you modify your first REPLACE statement so that it eliminates hyphens as well as left brackets. Once again, many thanks jdraw.
you can replace a contiguous set of characters with 1 or more characters. One set per Replace eg:"-(" or "(((---"

 

[JDubya]

Cheers jdraw for not only solving my problem but for taking the time to explain it as well. You've been a massive help with all this and I appreciate your help. All the best, John.

 

[jdraw]

John,

Happy to help. Good luck with your project.