Translate, search & replace, scrubbing data

["Dray, Adam" <Adam.Dray@PHH.COM>]

A note about translate, search&replace, and scrubbing phone numbers...

--------------------------------------------------------------------------

[Debra Benton <debra.benton@npac.com> asks how to search & replace in SQR]

There is no search and replace function in SQR, as far as I know.  You
could write a procedure to do this with a little thought and some careful
testing.  Use instr() to search, substr() to replace, and || to put it
all together.  Be careful not to recurse over the text you replaced the
old text with.  My algorithm:


! Replace all instances of 'search string' in 'source string' and return
! the result in 'result string'.
!
SEARCH_AND_REPLACE(source string, search string, :result string)
    Set the result string to null
    While the source string is not null
        Search for the first occurrence of the search string in the source
        If a match was found, then
            Break the source string into three parts:
              1) the stuff before the match
              2) the text that matches the search string
              3) the stuff after the match
            Append the stuff before the match to the result string
            Append the replacement string to the result string
            Set the source string to the stuff after the match
        End If
    End While
    Return the result string
End Procedure

--------------------------------------------------------------------------

[Sam Spritzer <SSpritzer@gw.ctg.com> asks how to scrub phone numbers]

There is indeed a recent thread in the SQR-UG archives.  The gist of it
was to loop over the phone number string a character at a time to remove
anything not in 0-9.  In other words, brute force and not elegance prevails.

If you're sure that you can predict the range of non-numeric characters
that get inserted into phone numbers, you can use translate this way:

  Let $bad_chars = '-/ abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
  Let $clean_phoneno = translate($phoneno, $bad_chars, '')

Of course, if you get a character in a phone number that does not appear
in $bad_chars, it won't be scrubbed out.  You might be able to loop
through the ascii range and append chr() values to a $bad_chars string
to generate a more complete list of scrubbable values.  Just be sure to
exclude 0-9. ;)