CODEWITHSUNDEEP
regexoracle-databaseoracle11gregexp-replace
Rak kundra
Rak kundra

Reputation: 178

How to replace the new line and line feed in a specific manner

I have the below data in a column called as "DATA" in oracle.I was trying to remove the new empty line after each line .

I have tried using

Query :

Select regexp_replace(regexp_replace(data,chr(13),null),chr(10),null) from main_data;

When i execute the below query the output is like a paragraph .

This is a text from line 1.This is text from line 2.This is a text from line .The line 3 ends here .This is a text from line 4.The line ends here .

Can any one say how to achieve this ?

Upvotes: 2

Views: 3342

Answers (3)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627087

You may use a '(' || chr(13) || chr(10) || '?|' || chr(10) || '){2,}' regex:

select regexp_replace(
    'This is a text from line 1.' || chr(13) || chr(10) || chr(13) || chr(10) || 'This is text from line 2.' || chr(10)  || chr(10) || 'This is a text from line 3.The line 3 ends here .'  || chr(10) || chr(10) || 'This is a text from line 4.The line ends here .',
    '(' || chr(13) || chr(10) || '?|' || chr(10) || '){2,}',
    '\1') as Result from dual

The pattern matches 2 or more ({2,}) consecutive repetitions of a CR symbol followed with an optional (1 or zero, ?) LF symbol or (|) an LF symbol.

The match is replaced with the single, last matched occurrence of CRLF, CR or LF as \1 is a placeholder for the value captured with the capturing group #1 (the first (...) construct in the pattern).

Output of the online demo:

enter image description here

Upvotes: 3

Visionary Zen
Visionary Zen

Reputation: 80

How about you replace the the occurrence of consecutive end of line markers with just one.

DECLARE

  DATA VARCHAR2(2000);
BEGIN
  DATA := 'This is a text from line 1.' || CHR(13) || CHR(13) ||
          'This is text from line 2.' || CHR(13) || CHR(13) ||
          'This is a text from line 3.The line 3 ends here .' || CHR(13) ||CHR(13) ||
          'This is a text from line 4.The line ends here .';

  dbms_output.put_line(regexp_replace(DATA, chr(13) || CHR(13), CHR(13)));

END;

This will give you

This is a text from line 1.
This is text from line 2.
This is a text from line 3.The line 3 ends here .
This is a text from line 4.The line ends here .

Upvotes: 0

Matt
Matt

Reputation: 15061

If the fields are blank or NULL use the below.

SELECT DATA
FROM main_data
WHERE DATA IS NOT NULL OR DATA != ''

Upvotes: 0

Related Questions