Hi,

I am trying to identify invalid characters in a table. The valid
characters are stored within a table which the table I want to find
invalid characters are matched upon. The table pub_symbol consists of
all valid characters (chr(value)), while the table address is the
table I want to check. This is what I have so far:

select addrsurname from address minus select a.addrsurname from
address a, pub_symbol p where instr(a.addrsurname, p.pub_symbol)<>0;

This (I hope) will give me all addrsurname which contain any
characters not listed in pub_symbol. Does anyone have any propositions
on how to optimize this,- or another way of achieving the result on?

Thanks,
Kenneth

On 15 jun, 10:15, t538449 <kenneth.osenbr...@telenor.com> wrote:
> Hi,
>
> I am trying to identify invalid characters in a table. The valid
> characters are stored within a table which the table I want to find
> invalid characters are matched upon. The table pub_symbol consists of
> all valid characters (chr(value)), while the table address is the
> table I want to check. This is what I have so far:
>
> select addrsurname from address minus select a.addrsurname from
> address a, pub_symbol p where instr(a.addrsurname, p.pub_symbol)<>0;
>
> This (I hope) will give me all addrsurname which contain any
> characters not listed in pub_symbol. Does anyone have any propositions
> on how to optimize this,- or another way of achieving the result on?
>
> Thanks,
> Kenneth

You might want to take a look at TRANSLATE function.

HTH.

Cheers.

Carlos.

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
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 15 Jun, 17:48, DA Morgan <damor...@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...@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æøåABCDEFGHIJ KLMNOPQRSTUVWXYZÆØÅéÉüÜöÖäÄ ,;.:-
_!"#¤%&/()=?\*¨^`@£${[]}´<>|§~',
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