I need to replace all instances where a new line is not followed by a colon. This is because colon indicates the start of a new record (a record may span multiple lines).
REGEXP_REPLACE(raw,'(\n[^:])','this is a test')
For example, with the input:
:[MY DATA HERE]
[rest of record printed here]
produces the output:
:[MY DATA HERE]
this is a testrest of record printed here]
instead of:
:[MY DATA HERE]this is a test[rest of record printed here]
Note that the current output replaces the first non ':' character but not the new line. Does anyone know why this is not working as expected?
Thanks in advance
You're replacing the newline and the character after it. You need to capture the character after it, and put that in the replacement. You can refer to capture groups using \number
REGEXP_REPLACE(raw,'\n([^:])','this is a test\1')
See the documentation
Exasol also supports PCRE, so you should be able to use a negative lookahead:
REPLACE_REGEXP(raw, '\n(?!:)', 'this is a test')
These differ in one subtle way: Your regexp won't match a newline at the end of the string, since there's nothing to match [^:] there. The negative lookahead will match at the end.