Oracle reexpression to display unique character differences between two lines

Question

In Oracle 10g, I would like to create a regular expression to display characters that are between two lines.

Here's why: I have a table with a field that sometimes uses Unicode characters that are not in French.

I can list the lines containing these non-standard characters in order to do future cleanup with this query:

SELECT DataID, Name, CONVERT(NAME, 'WE8ISO8859P1', 'WE8DEC')  
  FROM table
  WHERE NAME <> CONVERT(NAME, 'WE8ISO8859P1', 'WE8DEC' )

where WE8ISO8859P1 is Western European (which I accept)

and WE8DEC - 8-bit character sets from Digital Equipment Corporation (what I know is application support)

I assume that with Oracle regex, I could extract a list of all these non-standard characters. But I am not familiar with regexp in Oracle, so any help would be appreciated.

Here is my (not working) idea:

select regexp_replace("éaé", '[a-z][A-Z]', '' ) from dual;

will give "é" as a character to clear.

Answer 1

Perhaps something like this might make you move in the right direction:

SQL> select regexp_replace('éaéABcdEF', '([a-zA-Z])', '' ) problems from dual;

PROBLEMS
--------
éé

It gives you every occurrence of the characters you want to identify, but maybe this is not a problem or you can refine it ...

Answer 2

I think your only mistake was to use double quotes for the first parameter in your question. Usually double quotes are used only for column / table names.

, DCookie, . , , replace, puncuation ( ). :

SELECT regexp_replace(name, '([a-zA-Z ,.;''"])\-?','') problem_characters , count(*) 
FROM table
  WHERE NAME <> CONVERT(NAME, 'WE8ISO8859P1', 'WE8DEC' )
group by regexp_replace(name, '([a-zA-Z ,.;''"])\-?','');

Answer 3

SELECT LISTAGG( letter, '' ) WITHIN GROUP(ORDER BY letter) 
       FROM ( SELECT DISTINCT substr( 'aaaaabcde', level, 1 ) letter 
                     FROM dual CONNECT BY level <= length('aaaaabcde') )