replaceall regex does not produce result in trino CLI

[natarajank11]

[natarajank11]

I am working on Trino UDF (version 407). Basically i want to remove '/r/n','/r' and '/n' from the string.

The below code does not work if regex with replaceAll is used in Trino.

`
import io.airlift.slice.Slice;
import io.airlift.slice.Slices;
import io.trino.spi.function.Description;
import io.trino.spi.function.ScalarFunction;
import io.trino.spi.function.SqlType;
import io.trino.spi.type.StandardTypes;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class RpNewLine
{
private RpNewLine()
{}

@Description("Replace newline code with single-byte space")
@ScalarFunction("rnc")
@SqlType(StandardTypes.VARCHAR)
public static Slice rmcc(@SqlType(StandardTypes.VARCHAR) Slice string)
{
    String text = string.toStringUtf8();
    String regex = "(\\x0D\\x0A|\\x0D|\\x0A)";
    Pattern p = Pattern.compile(regex);
    Matcher m = p.matcher(text);
    String result = m.replaceAll(" ");

    return Slices.utf8Slice(result);
}

}

`

If i test in trino, the result is same as the input as shown below.
trino> select rnc('\nhello\r\nhello\rhello\nhello'); => \nhello\r\nhello\rhello\nhello

The same logic works fine and produce result with the replace function.
String result = text.replace("\r\n", " ").replace("\r", " ").replace("\n", " ");
output: hello1 hello2 hello3 hello4

But i don't want to use replace function as i have other regex pattern functionality to solve.
I want to use regex pattern and achieve in replaceall function. please help if i am missing anything.

[hashhar]

I don't mean for you to change the regex. I mean that in the query the values are treated as literals - that's how SQL works.

So with your existing regex like String regex = "\\R" (\R matches all newlines since JDK 8) if you call the function with input like SELECT rep_newline_code(U&'hello\000Aworld') you'll get output as 'hello world'.

[hashhar]

If your input has "escape" characters instead of real newlines then your regex would need to be String regex = "\\r\\n|\\n\\r|\\r|\\n".

Also why don't you log the value of text to see what input your function actually receives.

[natarajank11]

The value for text is perfect output. when i try to use udf in trino, then the result are wrong.

[chenjian2664]

@natarajank11 How about using the regex String regex = (\\\\r\\\\n|\\\\n\\\\r|\\\\r|\\\\n)

[natarajank11]

using the regex pattern (\\\\r\\\\n|\\\\n\\\\r|\\\\r|\\\\n) is working as expected.
Thank you!!

[natarajank11]

I have similar problem with control characters, this time changed based on the above solution String regex = "[\\\\p{C}]";.
However this does not produce result.
Trino result:
select rm_code('hello\bhello\thello'); => hellobhellothello

Before the change, using String regex = "[\\p{C}]";, which produced,
select rm_code('hello\bhello\thello'); => hello\bhello\thello

[natarajank11]

Just in case, i have created new ticket, #17614