Oracle: How can I tell what newline (or low-ascii chars) are in my text fields?

I have data in some VARCHAR2 fields that con开发者_运维知识库tains embedded newline chars (see this question)

However, it seems that data differs in the way that it stores the embedded newline chars - some of the data goes back to the mid 90's, so that's not surprising.

How can I see exactly what characters are embedded in my fields? I've tried both sqldeveloper and sql*plus.

The easiest option is to

SELECT your_column_name, dump( your_column_name, 1013 )
  FROM your_table_name

That will show you the decimal value of each character stored in that column. So, for example

SQL> ed
Wrote file afiedt.buf

  1  select ename, dump(ename, 1013) dmp
  2*   from emp
SQL> /

ENAME      DMP
---------- -------------------------------------------------------
smith      Typ=1 Len=5 CharacterSet=AL32UTF8: 115,109,105,116,104
ALLEN      Typ=1 Len=5 CharacterSet=AL32UTF8: 65,76,76,69,78
WARD       Typ=1 Len=4 CharacterSet=AL32UTF8: 87,65,82,68
JONES      Typ=1 Len=5 CharacterSet=AL32UTF8: 74,79,78,69,83
MARTIN     Typ=1 Len=6 CharacterSet=AL32UTF8: 77,65,82,84,73,78
BLAKE      Typ=1 Len=5 CharacterSet=AL32UTF8: 66,76,65,75,69
CLARK      Typ=1 Len=5 CharacterSet=AL32UTF8: 67,76,65,82,75
SCOTT      Typ=1 Len=5 CharacterSet=AL32UTF8: 83,67,79,84,84
KING       Typ=1 Len=4 CharacterSet=AL32UTF8: 75,73,78,71
TURNER     Typ=1 Len=6 CharacterSet=AL32UTF8: 84,85,82,78,69,82
ADAMS      Typ=1 Len=5 CharacterSet=AL32UTF8: 65,68,65,77,83
SM0        Typ=1 Len=3 CharacterSet=AL32UTF8: 83,77,48
FORD       Typ=1 Len=4 CharacterSet=AL32UTF8: 70,79,82,68
MILLER     Typ=1 Len=6 CharacterSet=AL32UTF8: 77,73,76,76,69,82
FOO        Typ=1 Len=3 CharacterSet=AL32UTF8: 70,79,79

15 rows selected.

See here for a way to grab only rows with column having certain chars. Easily converted to showing non-printable chars only, or whatever you wish to view.

See my answer using regexp_instr function.

Hope that helps