Identify records that are non ascii character

[jasonhuibers]

In my table Table1 , there is a column in which we need to identify all non ascii character? We can run the query and provide the list of records in which there is a non ascii character and the data can be cleaned up the by customer. How can I do this in a query?

 

[stefanhei]

Hi,
the asciistr function can help you there:

SELECT *
  FROM table1
 WHERE column1 != asciistr(column1);

 

[Dagon]

Do you literally mean or non-Ascii character here or do you mean something like a non-alphabetic/non-numeric character?

 

[jasonhuibers]

For example: ´

 

[stefanhei]

The character ´ has ascii value 180 and actually is an ascii character that would be recogized by my query as such. A query that returns all records that contain characters not in a given set can use the replace function:

SELECT *
  FROM table1
 WHERE REPLACE(column1,'<all_allowed_characters_here>') IS NOT NULL;

 

[Dagon]

That is slightly different to a non-ASCII character. A non-ASCII character would be something from a different character set e.g. EBCDIC. What you mean is that you want to remove all non-alphabetic characters.

As you don't have Oracle 10 (I assume you are on Oracle 9 since you posted to that forum), you can't use regular expressions. However, you could use something like translate:

     select name, translate(upper(name), '~ABCDEFGHIJKLMNOPQRSTUVWXYZ', '~')
     from
     (select 'fred' name
     from dual
     union
     select 'Bill^'
     from dual
     union
     select 'J*oe'
     from dual)
     where translate(upper(name), '~ABCDEFGHIJKLMNOPQRSTUVWXYZ', '~') is not null

With Oracle 10 onwards, you can use regular expressions through regexp_like:

select name
     from
     (select 'fred' name
     from dual
     union
     select 'Bill^'
     from dual
     union
     select 'J*oe'
     from dual)
     where regexp_like(name, '[^A-Z]', 'i')