CODEWITHSUNDEEP
oracle-databaselinefeed
Ashwin
Ashwin

Reputation: 247

RegEx to remove new line feed at starting and ending in Oracle SQL

My data contains lots of new line feeds at starting and end of string, which is making difficulty in understanding data. Sample data is shown as below.

'

sample text

new value

'

I tried to replace chr(10) with empty string, but it was removing line feed between two lines which is required.

My Code

select REPLACE(a1, chr(10), ' ') from( select  '

Sample Text

New value

' as a1 from dual);

Any suggestions.

Upvotes: 0

Views: 7925

Answers (2)

Sam
Sam

Reputation: 77

Another approach to trim or remove the line feed/carriage returns/tab is using "translate(column_name, chr(10) || chr(13) || chr(09), ' ') ". This worked for me.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

Try using TRIM:

SELECT
    TRIM(CHR(10) FROM col)
FROM dual

TRIM has the option to specify a character to remove from the beginning and ending of the string. Follow the short demo below to see it in action. Click here to see how to use TRIM with a number of examples.

Edit: If you have a CLOB column and TRIM won't work, then we can still use REGEXP_REPLACE:

SELECT REGEXP_REPLACE(REGEXP_REPLACE(col, '^' || CHR(10) || '*', ''),
    CHR(10) || '*$', '') AS output
FROM dual

Here is a demo showing that REGEXP_REPLACE works in place of TRIM:

Demo

Upvotes: 3

Related Questions