Re: Identify invalid characters in table.

From: t538449 <kenneth.osenbroch_at_telenor.com>
Date: Mon, 18 Jun 2007 01:57:53 -0700
Message-ID: <1182157073.245981.178960@p77g2000hsh.googlegroups.com>

On 15 Jun, 17:48, DA Morgan <damor..._at_psoug.org> wrote:
> t538449 wrote:
> > Hi,
>
> > I am trying to identify invalid characters in a table.
>
> In a row?
> In a field?
> VARCHAR2?
> CLOB?
> LONG?
> Version?
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Hi,

I solved the problem by using a function I found at AskTom (bless him):

CREATE OR REPLACE FUNCTION "IBSADMIN"."STRIP_BAD" (ar_str

    varchar2,

                        ar_good varchar2 default

'0123456789abcdefghijklmnopqrstuvwxyzæøåABCDEFGHIJKLMNOPQRSTUVWXYZÆØÅéÉüÜöÖäÄ ,;.:- _!"#¤%&/()=?\*¨^`@£${[]}´<>|§~',

                        ar_placeholder varchar2 default chr(1))

return varchar2
as

  v_result varchar2(4000);
  v_char varchar2(1);
  v_len number;

begin
  v_result := ar_str;
  v_len := nvl(length(v_result), 0);
  for i in 1..v_len loop
    v_char := substr(v_result, i, 1);
    if v_char <> ar_placeholder and instr(ar_good, v_char) = 0 then       v_result := replace(v_char, ar_placeholder);     end if;
  end loop;
   v_result := replace(v_result, ar_placeholder);   return v_result;
end;
/

By issuing a select like the following, I can identify any character not listed in the ar_good string:

select addrcustnr, addrsurname, strip_bad(addrsurname) from address where addrsurname <> ' '
and addrsurname <> strip_bad(addrsurname) group by addrcustnr, addrsurname, strip_bad(addrsurname);

Thanks,
Kenneth Received on Mon Jun 18 2007 - 03:57:53 CDT