some SQL functions that could help. You can use the HEX function to return
the hexadecimal representation of the characters in the varchar column. I know
that UDB running on UNIX and Windows uses ASCII as its character set, but
I'm not sure if the codepage and code set will actually affect what hexadecimal
value is used for what character. For instance, X'20' represents a space, X'0D'
is a carriage return, and X'0A' is a line feed in ASCII normally. I don't know if
the codepage and code set will ever affect that. In UDB Version 6.1 the
codepage and code set are at the database level and can be determine by
the following command:
db2 get db cfg for databasename | grep code
I believe in Version 7, the codepage can be different for each table, but I'm
not positive about that.
The fun begins when you start to search for the special characters.
The
special characters are not just in one group of hexadecimal values.
Some of
the values are from X'00' - X'2F', another is from X'3A' - X'40',
another is from X'5B' - X'60', another is from X'7B' - X'7F'.
The other
difficulty is parsing through the string of hexadecimal characters
returned
by the HEX function. You could use the SUBSTR function to break
the
string into one-character strings, but with a varchar column the length
of
the string isn't constant. This would require the use of some
other functions,
like LENGTH.
It looks like solving your problem could create a really ugly SQL and
might
be easier to solve with SQL and some kind of language that deals well
with
string parsing or array processing, if you chose to store the string
in an array.
You might want to look at using Perl, C or Java to help you with the
problem.
Amir Sadeghi wrote:
Team,In one of our varchar columns, we are seeing some special characters such as
Carriage Return, Line Feed, etc.Is there an easy way to search for these characters in the column, possibly
using SQL?Thanks in advance.
Amir Sadeghi
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
