Need help to remove tbas, carriage retrun, and other!!!
From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 04/29/04
- Next message: MGFoster: "Re: Query Problems"
- Previous message: tina: "Re: Switch is overloaded!"
- In reply to: Will: "Need help to remove tbas, carriage retrun, and other!!!"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 29 Apr 2004 13:07:50 -0700
Hi Will,
To use the replace function, you need to find out the
ASCII code of the character (you can use the asc()
function) and use the chr() function to specify it in
other functions (such as replace() or instr()). In other
words, instead of typing something such as "t" (which
would replace the t's), you would type chr(10) (or
whatever your ASCII code is - mine is 10 in the Oracle db
that I link to) to specify the string that you want to
replace.
Oracle uses these to delimit array fields. If you like
you can also write a custom function to return just
one "field" of the array. One that I use is pasted below
if you are interested (titled parsearray()). To call
this function, you just enter the field reference, the
number of the "field" number that you want to return, and
optionally the ascii chr number of the delimiter
(defaults to 10) and "Y" or "N" to specify whether to add
a prefix of the field number and a dash to the string
(defaults to no). You can use this function to break the
contents of the array field into separate columns in a
query if you have that need.
Here's the code:
Public Function ParseArray(InputArrayField, OutputFieldNo
As Integer, Optional DelimiterCharCode As Integer = 10,
Optional InclFieldNo_Y_N As String = "N")
Dim ArrayBreakPos() As Integer, strPrefix As String
On Error GoTo ErrorHandler
If IsNull(InputArrayField) = True Then
ParseArray = Null
Exit Function
End If
If UCase(Left(InclFieldNo_Y_N, 1)) = "Y" Then
strPrefix = OutputFieldNo & " - "
Else
strPrefix = ""
End If
ReDim ArrayBreakPos(OutputFieldNo) As Integer
'Set the Break Position 0 (which is not a real break) as
0 to start searching the string initially
'At the start point of the string (1 character after the
previous break pos)
ArrayBreakPos(0) = 0
For i = 1 To OutputFieldNo
ArrayBreakPos(i) = InStr(ArrayBreakPos(i - 1) + 1,
InputArrayField, Chr(DelimiterCharCode), vbTextCompare)
If ArrayBreakPos(i) = 0 Then
If i < OutputFieldNo Then
ParseArray = Null
Else
ParseArray = strPrefix & Right
(InputArrayField, Len(InputArrayField) - ArrayBreakPos
(OutputFieldNo - 1))
End If
Exit Function
End If
Next
ParseArray = strPrefix & Mid(InputArrayField,
ArrayBreakPos(OutputFieldNo - 1) + 1, ArrayBreakPos
(OutputFieldNo) - ArrayBreakPos(OutputFieldNo - 1) - 1)
Exit Function
ErrorHandler:
ParseArray = "Error - " & Err.Number & " " &
Err.Description
End Function
Hope that helps. Post back if it doesn't, or if you have
further questions.
-Ted Allen
>-----Original Message-----
>Hi all,
>
>does anyone know the way to remove non-printable
>characters from the text string?
>I have a value that was imported from Oracle and it
>contains that "square" box to separate string inside of
>the value.
>How to remove it or replace with another character?
>I have tried to use REPLACE, but no luck.
>Thank you very much!!
>
>W
>.
>
- Next message: MGFoster: "Re: Query Problems"
- Previous message: tina: "Re: Switch is overloaded!"
- In reply to: Will: "Need help to remove tbas, carriage retrun, and other!!!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
