CODEWITHSUNDEEP
sqloracle-database
BJH
BJH

Reputation: 45

How to remove everything in a field after an newline or carriage return

I have a database table which occasionally contains fields in which the data is repeated with a newline or carriage rturn in between.

e.g.the field contains

John Smith
John Smith

Instead of just:

John Smith

I thought that some sort of regexp_replace, as below, would work to update the field and remove both the newline/CR and the text after it, but I can't get it to work.

UPDATE TABLE
SET field =REGEXP_REPLACE(NAME, chr(13), '')
WHERE    ACCOUNT   = 123456

I expect the update to turn 

John Smith
John Smith

into 

John Smith

Upvotes: 1

Views: 110

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I think you want:

UPDATE TABLE
    SET field = REGEXP_SUBSTR(NAME, '^[^' || CHAR(13) || ']*')
    WHERE ACCOUNT = 123456;

Of course, this returns the first line. If you want the last line:

UPDATE TABLE
    SET field = REGEXP_SUBSTR(NAME, '[^\n]*$')
    WHERE ACCOUNT = 123456;

Upvotes: 0

GMB
GMB

Reputation: 222672

Consider:

UPDATE my_table 
SET my_column = REGEXP_REPLACE(my_column , chr(13) || '.*$', '', 1, 0, 'n') 
WHERE ACCOUNT = 123456

Rationale :

  • chr(13) || '.*' matches from the (first) new line to the end of string (greadily)
  • the matching part of the string will be replaced by the empty string
  • the final 'n' option allows the dot character to match on newlines (by default it doesn't)

Demo on DB Fiddle:

WITH a AS (SELECT 'John Smith' || chr(13) || 'John Smith' txt FROM DUAL)
SELECT 
    a.txt,
    REGEXP_REPLACE(a.txt, chr(13) || '.*', '', 1, 0, 'n') new_txt
FROM a;

TXT                   | NEW_TXT   
:-------------------- | :---------
John Smith            | John Smith 
John Smith            |

Note: as commented by @Alex Poole, if you want to match on both new line and carriage return, you can use :

'('||chr(13)||'|'||chr(10)||').*'

Upvotes: 2

Related Questions