Non ASCII character

I have a function that cleans up NoN Ascii characaters but I came across this bad boy (┬╡)and when I try to run the function that does the cleaning of Non ASCII characters it prompts me for a replacement value for the word MARBLE?

Select clean_non_ascii('‘JOHNS TILE ┬╡& MARBLE INSTALLATION INC                         ‘') From Dual

The function looks like this


function  clean_non_ascii(input_str             in varchar2,
                          usetable              in number default 0,
                          use90                 in number default 0)

return varchar2 as

str VARCHAR2(100 BYTE);  --Extra 2 Bytes for ^
str60 VARCHAR2(70 BYTE); -- Extra 2 BYTES for ^
act number :=0;
cnt number :=0;
askey number :=0;
OUTPUT_STR VARCHAR2(90 BYTE);
OUTPUT_STR_60 VARCHAR2(60 BYTE);
err_code NUMBER;
err_msg VARCHAR2(200);  

  begin

      str:= '^'||TO_CHAR(INPUT_STR)||'^';
      cnt:=length(str);
      if length(cnt) = 0 then
        for i in 1 .. cnt loop
          askey :=0;
          select ascii(substr(str,i,1)) into askey
          from dual;
          if askey < 32 or askey >=127 then
            str := '^'||REPLACE(str, CHR(askey),'');
          end if;
        end loop;
        OUTPUT_STR := trim(ltrim(rtrim(trim(str),'^'),'^'));
      else
        OUTPUT_STR := INPUT_STR;
      end if;

      RETURN (OUTPUT_STR);

  end clean_non_ascii;
jknj72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
In sqlplus/sql developer:
set define off

It sees the & as a substitution variable.
slightwv (䄆 Netminder) Commented:
Can you not use regular expressions?

regexp_replace(input_str,'[^[:print:]]')
awking00Information Technology SpecialistCommented:
Just to demonstrate slightwv's solution -
SQL> select 'JOHNS TILE ┬╡& MARBLE INSTALLATION INC' from dual;
Enter value for marble: GRANITE
old   1: select 'JOHNS TILE ┬╡& MARBLE INSTALLATION INC' from dual
new   1: select 'JOHNS TILE ┬╡GRANITE INSTALLATION INC' from dual

'JOHNSTILE┬┐GRANITEINSTALLATIONINC'
---------------------------------------
JOHNS TILE ┬╡GRANITE INSTALLATION INC

SQL> SET DEFINE OFF
SQL> select 'JOHNS TILE ┬╡& MARBLE INSTALLATION INC' from dual;

'JOHNSTILE┬┐&MARBLEINSTALLATIONINC'
----------------------------------------
JOHNS TILE ┬╡& MARBLE INSTALLATION INC
No points please.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jknj72Author Commented:
Where would I put the SET DEFINE OFF in the function? And slight I sure can use Regex_Replace. DO I just use that instead of Replace? Keep all the other code?
slightwv (䄆 Netminder) Commented:
>>Where would I put the SET DEFINE OFF in the function?

You don't need it in the function.  You only need it in sqlplus to properly call the function.

>>DO I just use that instead of Replace?

Yes.

>>Keep all the other code?

The rest of the code should be unnecessary.  The regexp_replace will work on the entire string.  No need to loop through one character at a time.

Looks like it is pretty much a one line function:
function  clean_non_ascii(input_str             in varchar2)
is
begin
return regexp_replace(input_str,'[^[:print:]]');
end;
/

Which leads to:  Do you need the user defined function at all?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
I guess I dont need the function and Im wondering why I went through all the characters.

If this....

return regexp_replace(input_str,'[^[:print:]]');

will bring me back only ASCII characters and take out NON ASCII chars then Im good to go...
slightwv (䄆 Netminder) Commented:
>>will bring me back only ASCII characters and take out NON ASCII chars then Im good to go...

The print class is all printable characters:
https://en.wikipedia.org/wiki/Regular_expression

[\x20-\x7E] Visible characters and the space character.
jknj72Author Commented:
I would want to replace the NON ASCII with a whitespace though. That was the logic I was trying to accomplish
slightwv (䄆 Netminder) Commented:
>>I would want to replace the NON ASCII with a whitespace though.

I understand that.  The '^' inside the brackets means 'NOT'.  So, what I posted replaces 'NOT printable'.

Did you try what I posted and compare it with what you had?
jknj72Author Commented:
Yes and it seems to work and if your saying thats what I need then Im good to go. I didnt know if it was also doing an ASCII check as well?
slightwv (䄆 Netminder) Commented:
It should do the same thing that you had.
jknj72Author Commented:
Thanks Slight
jknj72Author Commented:
Thanks again!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.