|
|
Suppose that we have following table with data that originated from the user
input (or was imported from some source):
| RecordID | Remark |
| 1 | // risk management |
| 2 | taking to the account credit crunch . # |
| 3 | errors made by the tycoons |
| 4 | partial 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
|
| RecordID | Remark |
| 1 | // risk management |
| 2 | taking to the account credit crunch .# |
| 3 | errors made by the tycoons |
| 4 | partial 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.
|