I will be out of the office until Thursday, August 2nd. I will respond to your E-mail
at that time. If you need immediate assistance, please contact the on-call DBA.
>>> db2eug 07/30/01 07:20 >>>
I will be out of the office until Thursday, August 2nd. I will respond to your E-mail
at that time. If you need immediate assistance, please contact the on-call DBA.
>>> db2eug 07/30/01 06:29 >>>
I can't think of an "easy" way in SQL to solve your problem, but there are
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
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
