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

advertisements

I have a procedure which takes 2 variables as input, one among them may have string characters separated by enter or space.

Space however is easy to figure out, but how to we figure out if the variable has a newline character in it.

I have tried using chr(10) but no use, because I have to recognize if the strings are separated by a newline character. By the way even chr(10) doesn't work to insert string divided with space.

select 'ABC' || chr(10) || 'DEF' as c from dual

I currently have no such variable as input so can't even experiment to narrow down to some solutions, Also the above query isn't running fine, I mean it's not giving output like below.

ABC
DEF

I have also searched for different oracle documentation, but didn't find any.

Help would be appreciated.


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>