I'm trying to replace a string (" | ") with a carriage return/ line feed. The SQL I'm using is:
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?
Try this it will solve your problem
In fact to replace a special character like | you have to precede it with a backslash \ character.
seems you're on TD14, for a simple replace like this (no regular expression needed) i would use
oReplace(TRANSFORM_RULE,' | ','0D0A'XC)