unprintable char

[kris123]

Hi Friends,

I try to audit the fullname column in my travel table to eliminate
the garbage typo error done by encoders, using this commnad (courtesy of Jurij ):

update TRAVEL set fullname=
translate(fullname,'A`1234567890-=[]\;,./~!@#$%^&*()_+{}|:<>?"''','A')
where TRANSLATE(fullname, '1ABCDEFGHIJKLMNOPQRSTUVWXYZÑ', '1') is not null;

My problem is, I encountered unprintable chars which i can not write
in my substitution list. Can you pls help me how to replace an unprintable char?

Thank you all in advance

[jmodic]

Originally posted by kris123
Hi Friends,

I try to audit the fullname column in my travel table to eliminate
the garbage typo error done by encoders, using this commnad (courtesy of Jurij ):

update TRAVEL set fullname=
translate(fullname,'A`1234567890-=[]\;,./~!@#$%^&*()_+{}|:<>?"''','A')
where TRANSLATE(fullname, '1ABCDEFGHIJKLMNOPQRSTUVWXYZÑ', '1') is not null;

My problem is, I encountered unprintable chars which i can not write
in my substitution list. Can you pls help me how to replace an unprintable char?

Thank you all in advance

You know the ASCII codes of those unprintable characters, right? Let's assume they are in the range from below ASCII 32. So you would simply use CHR() function to append them to the substitution list, like:

update TRAVEL set fullname=
  translate(fullname,'A`1234567890-=[]\;,./~!@#$%^&*()_+{}|:<>?"''' ||
            CHR(1) || CHR(2) || ... || CHR(32),'A') 
where TRANSLATE(fullname, '1ABCDEFGHIJKLMNOPQRSTUVWXYZÑ', '1') is not null;

[kris123]

Thanks kuya mahal ko ...so thats what i needed ||....
anyway i'm still analyzing it...
Can i use ascii(fullname) or asciistr to show the ascii value of each charaters in the fullname column?

Many many thanks

[jmodic]

ASCII() accepts single characters as input argument only. And ther's no ASCIISTR() function in Oracle's SQL AFAIK. But you can use DUMP(fullname) to list all ascii codes in the string. This function displays datatype code and the length of the imput parameter, followed by comma delimited ascii codes of all the characters in the parameter.

[kris123]

Thanks a lot my idol, my hero, my angel