Replace with carriage return/ line feed

Database
Enthusiast

Replace with carriage return/ line feed

I'm trying to replace a string (" | ") with a carriage return/ line feed.  The SQL I'm using is:

UPDATE AML_META_COLUMN_MASTER

SET TRANSFORM_RULE = REGEXP_REPLACE(TRANSFORM_RULE,' | ','0D0A'XC)

WHERE COLUMN_NAME like 'test%';

The query runs and executes on the two rows where this condition is true.  However, instead of replacing the string with a carriage return/ line feed the query prepends a carriage return/ line feed to the existing string.

Any thoughts as to what I'm doing wrong and about how I can accomplish what I'm trying to do?

TIA,

Nolan

3 REPLIES
Enthusiast

Re: Replace with carriage return/ line feed

Hi Nolan,

Try this it will solve your problem

SELECT REGEXP_REPLACE('A|b','\|','0D0A'XC);

In fact to replace a special character like | you have to precede it with a backslash \ character. 

Cheers :)

Khurram

Khurram
Junior Contributor

Re: Replace with carriage return/ line feed

Hi Nolan,

seems you're on TD14, for a simple replace like this (no regular expression needed) i would use

oReplace(TRANSFORM_RULE,' | ','0D0A'XC)

Dieter

Enthusiast

Re: Replace with carriage return/ line feed

Dieter,

No, I'm on TD 13.10.

The oReplace function works.

Thanks,

Nolan