CODEWITHSUNDEEP
sqloracle-databaseoracle11gnewline
Sam
Sam

Reputation: 2605

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

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.

Upvotes: 2

Views: 4780

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

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>

Upvotes: 4

anudeepks
anudeepks

Reputation: 1132

create table sample (a varchar2(100));
insert into sample (a) values ('HI_next_line
hello_next_line
hello2');
insert into sample (a) values ('singleline1');
insert into sample (a) values ('singleline2');

SQL> select * from sample;

A
-----------------------------------------------------------------------
HI_next_line
hello_next_line
hello2

singleline1
singleline2

SQL> select *
  2  from sample
  3  where instr(a, chr(10)) > 0;

A
-----------------------------------------------------------------------
HI_next_line
hello_next_line
hello2

Upvotes: 1

Related Questions