Search code examples
sqloracleoracle11gascii

Detect all special characters (ascii codes 128 to 255) on a varchar column

i have a table which has a description_text column (NVARCHAR) that i need to check for any special characters (from ascii codes 128 to 255).

What I wrote is:

SELECT cid as ID, description_id as "Element ID", description_text as Text, 'special characters in description_text (tbdescription)' as "Error"
FROM tbdescription d
WHERE
(
description_text LIKE '%' || CHR (129) || '%'
or description_text LIKE '%' || CHR (130) || '%'
//..and so on..//
)

Which does the job, but I'm sure there is more elegant way of verifying all these ascii codes without all the or conditions.

I use Oracle Client Version 11.1.0.6.0

Solution

  • You are almost there. where regexp_like(description_text, '(' || chr(128) || '-' || chr(255) || ')')

    Use hyphen instead of pipe in your regular expresssion.