Annie Jeba Annie Jeba - 1 month ago 9
SQL Question

Handle ' and & in SUBSTR Function

I have been asked to split comments field to 75 chars each and when I used substr function in a procedure to split all the comments, Comments having ' and & symbols are encountering exceptions.

Any help to split the data without any data loss is highly appreciated.

Sample data : Health Provider indicated in error that the employee DNA&d. The DNA charge has been removed.

Expected O/P: Health Provider indicated in error that the employee DNA&d. The DNA charge

MT0 MT0
Answer

Query

SET DEFINE OFF;

WITH data ( value ) AS (
  SELECT 'Health Provider indicated in error that the employee DNA&d. The DNA charge has been removed.'
  FROM DUAL
UNION ALL
  SELECT 'Apostrophe '' in this string'
  FROM DUAL
)
SELECT SUBSTR( value, 1, 75 ) AS substr
FROM   DATA;

Ouput:

SUBSTR                                                                    
---------------------------------------------------------------------------
Health Provider indicated in error that the employee DNA&d. The DNA charge  
Apostrophe ' in this string