CODEWITHSUNDEEP
oracle-database
vikram
vikram

Reputation: 69

How do I find out which columns and rows contain Extended ASCII codes?

Can any one help How to find out which columns and rows has Extended ASCII Codes in the table (EX:-Ž,™,Ù)?

Examples

jacob\Û

=pal®

I need query some thing like Select * from table to get Extended ASCII Thanks For help

Upvotes: 0

Views: 218

Answers (2)

GoranM
GoranM

Reputation: 165

You can try with:

SELECT *
FROM mytable
WHERE mycolumn<>CONVERT(mycolumn, 'US7ASCII');

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

You can use TRANSLATE to remove all valid characters from the string, so only the special characters remain. Then check for NULL (as an empty string is NULL in Oracle; don't use length, for the length will not be 0 as one would expect but null):

select name
from mytable
where TRANSLATE(name, '®ABCDEFG...abc...', '®') is not null;

You will have to put all valid characters in the string where I simply put '...'.

I used one special character to replace itself as you see, because otherwise the replacement string had to be empty, but empty means null in Oracle and translate doesn't work with null.

(Yes, that empty string is null thing in Oracle is really a nuisance.)

Upvotes: 1

Related Questions