SQL advanced -> MS-Access UDF function in that replaces special characters

Suppose that we have following table with data that originated from the user input (or was imported from some source):
RecordIDRemark
1// risk management
2taking    to the account credit crunch     . #
3errors made    by the tycoons
4partial recovery overshadowed by      //

The records are suffering from excidentally inserted blank spaces and other invisiball special characters, like TAB or Paragraph mark. Following VBA UDF replaces them by Chr(32):

Function fn_ReplaceSChar(ByVal strWord As String) As String
' this is VBA string for replacing special characters
' ASCII code (0 - 31)

  Dim strChar, strResult As String
  Dim intPos, intLen As Integer
On Error GoTo Err_SuperTrap
  intPos = 0
  intLen = Len(strWord)
  strResult = ""
If intLen = 0 Then
  fn_ReplaceSChar = strWord
  ' if string length = 0, return result now
Else
  For intPos = 1 To intLen
    strChar = Mid(strWord, intPos, 1)
    ' replace special char by blank space

    If Asc(strChar) < 32 Then
      strChar = Chr(32)
    End If

    strResult = strResult + strChar
  Next intPos
End If

' replace 2 blanks by one
strResult = Replace(strResult, Chr(32) & Chr(32) & Chr(32), Chr(32))
strResult = Trim(Replace(strResult, Chr(32) & Chr(32), Chr(32)))

fn_ReplaceSChar = strResult

Exit_SuperTrap:
   Exit Function

Err_SuperTrap:

   MsgBox Err.Description
   Resume Exit_SuperTrap
End Function

Then you can call this function from SQL statement, for example:
SELECT RecordID, fn_ReplaceSChar(remark) AS Clear_Remark
FROM user_remarks
WHERE RecordID < 5

RecordIDClear_Remark
1// risk management
2taking to the account credit crunch .#
3errors made by the tycoons
4partial recovery overshadowed by //

UDF functions can greatly enhance your SQL statements. If you are looking for ready VBA function, you can find huge archive of source code in "VBA Developer's Handbook" written by Ken Getz and Mike Gilbert.


sqlexamples.info