How to count length of line break as 2 characters in oracle sql

Question

I have data stored in table's column and it has a line break in the data. When I count the length of the string it returns me the count just fine. I want to make some changes and take the line break as 2 characters so if the data in table is something like this.

This
That

This should return length as 10 instead it is returning 9 for now which is understandable but I was to count the length of line break as 2 characters. So if there are 2 line breaks in data it will count them as 4 characters.

How can I achieve this ?

I want to use this in SUBSTR(COL, 1, 7) By counting line break as 2 character it should return data like this

This
T

Hope someone can help

Answer 1

Just replace new line in the string with 2 characters, for example 'xx', before counting string length. More info on how to replace new lines in Oracle: Oracle REPLACE() function isn't handling carriage-returns & line-feeds

Answer 2

Update your value to have a line feed character before the carriage return character.

So if you have the table:

CREATE TABLE test_data ( value VARCHAR2(20) );
INSERT INTO test_data ( value ) VALUES ( 'This
That' );

Then you can insert the LF before the CR:

UPDATE test_data
SET    value = REPLACE( value, CHR(10), CHR(13) || CHR(10) )
WHERE  INSTR( value, CHR(10) ) > 0

Then your query:

SELECT SUBSTR( value, 1, 7 ) FROM test_data;

Outputs:

| SUBSTR(VALUE,1,7) |
| :---------------- |
| This              |
| T                 |

db<>fiddle here