CODEWITHSUNDEEP
sqloracle-database
user14999956
user14999956

Reputation: 85

new lines are not getting eliminated

I'm trying to replace newline etc kind of values using regexp_replace. But when I open the result in query result window, I can still see the new lines in the text. Even when I copy the result, I can see new line characters. See output for example, I just copied from the result.

Below is my query

select regexp_replace('abc123     

/n
CHAR(10)

头疼,'||CHR(10)||'allo','[^[:alpha:][:digit:][ \t]]','') from dual;

/ I just kept for testing characters.

Output:

abc123     

/n
CHAR(10)

头疼,
allo

How to remove the new lines from the text?

Expected output:

abc123     /nCHAR(10)头疼,allo

Upvotes: 1

Views: 58

Answers (1)

user5683823
user5683823

Reputation:

There are two mistakes in your code. One of them causes the issue you noticed.

First, in a bracket expression, in Oracle regular expressions (which follow the POSIX standard), there are no escape sequences. You probably meant \t as escape sequence for tab - within the bracket expression. (Note also that in Oracle regular expressions, there are no escape sequences like \t and \n anyway. If you must preserve tabs, it can be done, but not like that.)

Second, regardless of this, you include two character classes, [:alpha:] and [:digit:], and also [ \t] in the (negated) bracket expression. The last one is not a character class, so the [ as well as the space, the backslash and the letter t are interpreted as literal characters - they stand in for themselves. The closing bracket, on the other hand, has special meaning. The first of your two closing brackets is interpreted as the end of the bracket expression; and the second closing bracket is interpreted as being an additional, literal character that must be matched! Since there is no such literal closing bracket anywhere in the string, nothing is replaced.

To fix both mistakes, replace [ \t] with the [:blank:] character class, which consists exactly of space and tab. (And, note that [:alpha:][:digit:] can be written more compactly as [:alnum:].)

Upvotes: 2

Related Questions