How to reveal hidden codes

Malcolm Dew-Jones · *nix forums Guru Joined: 04 Mar 2005 Posts: 418

cschang (cschang@maxinter.net) wrote:
: I am not a frequent user of oracle programmer. I remember seeing the
: solution about four years ago and I can not find from my memory and from
: the web. I will appreciate if anyone can help. What I have is one
: varchar2 column containing text records as 'No comment.'. I want to use
: the DECODE(val, 'No comment.', '', val) to display the empty or other
: kinds of text. But it won't work. Hence, I used the length function to
: check on the No comment. value in the column and did return as 11. I
: remember there was a function I can display the binary code of every
: character of the data in the field. I also manually checked such value
: with
: SQL>exec :str:='No comment.';
: execution successfully

: SQL>select DECODE(:str, 'No comment.', '', :str) comments from dual;
: -----------------------------------------------------------

: I am using 9 R2 on Windows 2K. Thanks.

To display "binary" data in a character string, google "oracle character
function". It returns references to things like the "dump" function, plus
many others.

--

This space not for rent.

Mark D Powell · *nix forums Guru Joined: 23 Apr 2005 Posts: 701

C, I am a little confused by you post. You say that decode is not
returning what you want but you did not show your code or the response.

You can code multiple tests in the decode or in a CASE statement
contained in your SELECT list or WHERE clause such as
decode(val,NULL,'No Value','No Comments','Comment Missing',val).

The INSTR function can be used to find a string within a character
column. It returns the position where the string starts so any value >
0 is a hit.

HTH -- Mark D Powell --

cschang · *nix forums beginner Joined: 05 Feb 2005 Posts: 20

I am not a frequent user of oracle programmer. I remember seeing the
solution about four years ago and I can not find from my memory and from
the web. I will appreciate if anyone can help. What I have is one
varchar2 column containing text records as 'No comment.'. I want to use
the DECODE(val, 'No comment.', '', val) to display the empty or other
kinds of text. But it won't work. Hence, I used the length function to
check on the No comment. value in the column and did return as 11. I
remember there was a function I can display the binary code of every
character of the data in the field. I also manually checked such value
with
SQL>exec :str:='No comment.';
execution successfully

SQL>select DECODE(:str, 'No comment.', '', :str) comments from dual;
-----------------------------------------------------------

I am using 9 R2 on Windows 2K. Thanks.

C Chang

Google