How to search new line char in oracle table?

Question

I have an issue with a file that's created from data (in format of bytes) returned from the database.
the problem is that there is a few newlines in the file.
i would like to know if there is a way to write a where clause to check if some record has a newline character?

Answer 1

Using the CHR function to look for the ASCII value:

select *
from your_table
where instr(your_text_col, chr(10)) > 0;

If you want to search for carriage returns, that would be chr(13).

Answer 2

You can write something like:

SELECT id
  FROM table_name
 WHERE field_name LIKE '%'||CHR(10)||'%'
;

(|| is the concatenation operator; CHR(10) is the tenth ASCII character, i.e. a newline.)

Answer 3

Depending on the platform, a newline will generally either be a CHR(10) (Unix) or a CHR(13) followed by a CHR(10) (Windows). There are other options for other more esoteric platforms, but 99.999% of the time, it will be one of these two.

You can search the data in a column looking for one or both characters

SELECT instr( column_name, CHR(10) ) position_of_first_lf,
       instr( column_name, CHR(13) || CHR(10) ) position_of_first_cr_lf
  FROM table_name
 WHERE instr( column_name, CHR(10) ) > 0