Post reply

remove characters from fields

  • August 9, 2005 at 7:15 am

    #67464

    Hi,I'mtrying to remove the parentheses from phone numbers in a table. Change: (906)224-9898 to: 9062249898.

    Any ideas? Thanks!

     

  • August 9, 2005 at 7:33 am

    #580707

    update tablename set phonenumber = replace(replace(phonenumber, '(', ''), ')', '')

  • August 9, 2005 at 7:48 am

    #580718

    Thanks it worked with one less replace:

    update tablename set phonenumber = replace(phonenumber, '(', ''), ')', '')

  • August 9, 2005 at 8:07 am

    #580730

    Are you sure this works?? I never heard of replace doing more than one s/r at once.

  • August 9, 2005 at 10:53 am

    #580808

    I ran 3 separate steps:

    update tablename set phonenumber = replace(phonenumber, '(','')

    update tablename set phonenumber = replace(phonenumber, ')','')

    update tablename set phonenumber = replace(phonenumber, '-','')

    Thanks again for your help!

  • August 9, 2005 at 11:20 am

    #580816

    Sounds more like it... btw it's faster to run it once with embeded replaces, makes less work for the server.

  • August 10, 2005 at 3:36 am

    #580946

    is it possible unwanted characters other than ()- might appear in your phone number should your users mistype something? If so you might want to process the string by only allowing characters 0123456789 in it, rather than removing specific other ones you know about.

    martin

  • August 10, 2005 at 7:34 am

    #581015

    Use the In String function - much better at removing unwanted characters. Just pass in the string that contains the unwanted character, then pass in the actual unwanted character to be removed.

     

    Function StripCharacter(sWord As String, sChar As String) As String

    'Strips the specified character from a text string, using

    'the "InStr" function

       Dim x As Long

       x = 1

       Do Until x = 0

          x = InStr(sWord, sChar)

          If x = 0 Then GoTo Complete

          sWord = VBA.Left(sWord, x - 1) & VBA.Mid(sWord, x + 1)

       Loop

    Complete:

       StripCharacter = sWord

    End Function

  • August 10, 2005 at 9:23 pm

    #581268

    I'm not sure if VB is a proper solution, but at least write the loop correctly:

       x = InStr(sWord, sChar)

       Do Until x = 0

          sWord = VBA.Left(sWord, x - 1) & VBA.Mid(sWord, x + 1)

          x = InStr(sWord, sChar)

       Loop

  • August 10, 2005 at 10:28 pm

    #581274

    Glad I'm not the only one to think that... I was to disgusted to even correct it .

  • August 11, 2005 at 12:45 pm

    #581536

    I don't write VB code, but based on other coding experience it looks like I wrote my do loop correction wrong.  Shouldn't that be

    Do WHILE x>0?

    Do Until x=0 would run through the loop once whether the test was true or not, wouldn't it?

  • August 11, 2005 at 12:53 pm

    #581538

    Don't remember,,, I usually use

    while X>0

    ...

    wend

    I forgot the other 11 versions of the loop .

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply