How to Search New Line Char in Oracle Table

How to search new line char in oracle table?

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).

how to check new line character for all columns of the table

Do you need to do this in PL/SQL? You could get the offending rows with plain SQL, like this:

select name, code, description,
       case when name        like '%' || chr(10) || '%' then 'name, '      end ||
       case when code        like '%' || chr(10) || '%' then 'code, '      end ||
       case when description like '%' || chr(10) || '%' then 'description' end
       as where_found
from group_details
where name        like '%' || chr(10) || '%'
   or code        like '%' || chr(10) || '%'
   or description like '%' || chr(10) || '%';

This will return all the rows where at least one of the columns contains a newline, and the calculated where_found column will tell you exactly which columns have those values. (They would also be clearly visible; the calculated column can be used for further processing, if needed.)

How to enter newline character in Oracle?

Chr(Number) should work for you.

select 'Hello' || chr(10) ||' world' from dual

Remember different platforms expect different new line characters:

• CHR(10) => LF, line feed (unix)
• CHR(13) => CR, carriage return (windows, together with LF)

How to Find or Replace New Line Character in ORACLE SQL Developer

When you open Find/Replace, then:

• enter \n into the search field
• enter , into the replace field
• push the Regular expresssion button in the toolbar (it is 4th in my 18.3 version, after Match Case, Whole word, Highlight)
• push the "Replace all" button in the toolbar

How to recognize if a variable contains a newline character in Oracle

A new line depends on the Operating system. In Unix based OS, it is CHR(10), in Windows it is CHR(13) followed by CHR(10).

You could use either of the following:

• LIKE '%'||chr(10)||'%'
• INSTR(column_name, chr(10)) > 0

Let's look at test cases in Windows OS:

SQL

Using LIKE

SQL> WITH DATA AS(
  2  SELECT 'ABC' || chr(10) || 'DEF' AS c FROM dual UNION ALL
  3  SELECT 'PQR' || ' ' || 'XYZ' AS c FROM dual UNION ALL
  4  SELECT 'QWE' || CHR(13) || 'RTY' AS c FROM dual UNION ALL
  5  SELECT 'no_space' AS c FROM dual
  6  )
  7  SELECT * FROM DATA WHERE c LIKE '%'||chr(10)||'%';

C
--------
ABC
DEF

SQL>

Using INSTR

SQL> WITH DATA AS(
  2  SELECT 'ABC' || chr(10) || 'DEF' AS c FROM dual UNION ALL
  3  SELECT 'PQR' || ' ' || 'XYZ' AS c FROM dual UNION ALL
  4  SELECT 'QWE' || CHR(13) || 'RTY' AS c FROM dual UNION ALL
  5  SELECT 'no_space' AS c FROM dual
  6  )
  7  SELECT * FROM DATA WHERE INSTR(c, chr(10)) > 0;

C
--------
ABC
DEF

SQL>

PL/SQL

Using LIKE

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    c VARCHAR2(100);
  3  BEGIN
  4    c:='ABC' || chr(10) || 'DEF';
  5    IF c LIKE '%'||chr(10)||'%' THEN
  6      dbms_output.put_line('found chr(10)');
  7    ELSE
  8      dbms_output.put_line('not found');
  9    END IF;
 10  END;
 11  /
found chr(10)

PL/SQL procedure successfully completed.

SQL>

Using INSTR

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    c VARCHAR2(100);
  3  BEGIN
  4    c:='ABC' || chr(10) || 'DEF';
  5    IF INSTR(c, chr(10)) > 0 THEN
  6      dbms_output.put_line('found chr(10)');
  7    ELSE
  8      dbms_output.put_line('not found');
  9    END IF;
 10  END;
 11  /
found chr(10)

PL/SQL procedure successfully completed.

SQL>

Searching for newlines in a Oracle DB using SQL 'like' clause

like '%'||chr(10)||'%' ought to work.

Oracle - How to find carriage return, new line and tab using REGEXP_LIKE?

You can use:

select c1
from   table_name
where  c1 LIKE '%' || chr(10) || '%'
or     c1 LIKE '%' || chr(13) || '%'
or     c1 LIKE '%' || chr(9) || '%';

or

select c1
from   table_name
where  regexp_like(c1, '[' || chr(10) || chr(13) || chr(9) || ']')

fiddle

---

where regexp_like(c1, '[\r\n\t]') does not work as you are matching any character that is \ or r or \ or n or \ or t (and not matching the perl-like character sets \r, \n or \t).

where regexp_like(c1, '[chr(10)|chr(13)]') does not wotk as you are matching any character that is c or h or r or ( or 1 or 0 or ) or | or c or h or r or ( or 1 or 3 or ) as you have a string literal and are not evaluating the contents of the literal. If you want to evaluate them as calls to the CHR function then it must be outside the string literal as the second example above.

Can we display chr(10) (newline) in the Query Result Window? (SQL Developer)

The default grid view collapses that kind of whitespace. Doubleclick on the cell and you can activate a textarea for that specific cell.

---

Related Topics