REGEXP REPLACE with backslashes in Spark-SQL

Question

I have a string containing \s\ keyword. Now, I want to replace it with NULL.

select string,REGEXP_REPLACE(string,'\\\s\\','') from test

But unable to replace with the above statement in spark sql

input: \s\help
output: help

want to use regexp_replace

(1) What database are you using? (2) `NULL` is not something to replace in a string. (3) Do you want `replace()`? — Gordon Linoff, Apr 29 '20 at 19:39
i want to replace with a blank..i'm working with sparksql. only regexp_replace is available. — satish g, Apr 29 '20 at 19:43
yes Jake. it is failing. An error occurred when executing the SQL command: select string,REGEXP_REPLACE(string,'\\s\\','') from test. Invalid operation: Incomplete escape sequence found. The error occurred while parsing the regular expression: — satish g, Apr 29 '20 at 19:46
In your comment, you have two backslashes after the `s`, in your question only one. there is a significant difference as in the question you escape your `'` and that will definitely break it. — Talon, Apr 29 '20 at 19:57
i tried all combinations. not working..can anyone pass the sql to do it? requirement is to use regexp_replace and remove keyword \s\ — satish g, Apr 29 '20 at 20:03
Edit your original post to show the exact input string, and the expected output. — Gary_W, Apr 29 '20 at 20:15
Recently I wrote an answer to a similar question; maybe it will help you: https://stackoverflow.com/questions/41765033/why-does-sparksql-require-two-literal-escape-backslashes-in-the-sql-query/71575079#71575079 — Vojta F, Mar 22 '22 at 15:57

score 5 · Answer 1 · answered Apr 29 '20 at 21:43

To replace one \ in the actual string you need to use \\\\ (4 backslashes) in the pattern of the regexep_replace. Please do look at https://stackoverflow.com/a/4025508/9042433 to understand why 4 backslashes are needed to replace just one backslash

So, the required statement would become like below

select name, regexp_replace(name, '\\\\s\\\\', '') from test

Below screenshot has examples for better understanding

REGEXP REPLACE with backslashes in Spark-SQL

1 Answers1