SUBSTR in oracle not working when there is line break in the string

Okay so I am working with a database that has data in many formats. I was asked to split a string into 3 columns by 150 length in each column

The data in the table is stored as this

'1. These MSDS, Declaration and SGS are to post to Environment database.
        The MSDS serial number is MCSPRINGH0403021.
        The Declaration serial number is DCSPRINGH0403004.
        The SGS serial number is TCSPRINGH0403007.
    2. It's required to follow specification drawing with Rev.A0 for MP.
        The change for Rev.A0 is only change Rev."PA4" to "A0"  on silkscreen printing.


        '

I want to split this data into 3 columns , COL1, COL2, COL3

When I use this query it works fine.

SELECT SUBSTR('1. These MSDS, Declaration and SGS are to post to Environment database.
    The MSDS serial number is MCSPRINGH0403021.
    The Declaration serial number is DCSPRINGH0403004.
    The SGS serial number is TCSPRINGH0403007.
2. It''s required to follow specification drawing with Rev.A0 for MP.
    The change for Rev.A0 is only change Rev."PA4" to "A0"  on silkscreen printing.


    ', 1, 140) col1, 
    SUBSTR('1. These MSDS, Declaration and SGS are to post to Environment database.
    The MSDS serial number is MCSPRINGH0403021.
    The Declaration serial number is DCSPRINGH0403004.
    The SGS serial number is TCSPRINGH0403007.
2. It''s required to follow specification drawing with Rev.A0 for MP.
    The change for Rev.A0 is only change Rev."PA4" to "A0"  on silkscreen printing.


    ', 141, 280) col2,
    SUBSTR('1. These MSDS, Declaration and SGS are to post to Environment database.
    The MSDS serial number is MCSPRINGH0403021.
    The Declaration serial number is DCSPRINGH0403004.
    The SGS serial number is TCSPRINGH0403007.
2. It''s required to follow specification drawing with Rev.A0 for MP.
    The change for Rev.A0 is only change Rev."PA4" to "A0"  on silkscreen printing.


    ', 281, 420) col3 FROM DUAL

but I had to replace It's with It''s. How do I replace when the value is stored in column but that will not count ' as a character and will eventually return wrong count. Also when there is a line break I want it to be take as 2 character /b.

Cheers!

Answer

When you actually represent the data as a column in a query or DML command then you shouldn’t have that problem. In other words, when you do this:

select original_col,
       substr(original_col,1,140) col1,
       substr(original_col,141,140) col2,
       substr(original_col,281,140) col3
  from table;

it should work fine. You only have to worry about escaping special characters when you are typing in the literal text yourself, as in your example.

Also, be aware the the second number in substr is the length of the string, not the ending position of the segment, so if you want 140 characters, this should always be 140. From the documentation, “The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long.”

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SUBSTR.html#GUID-C8A20B57-C647-4649-A379-8651AA97187E