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
– Gordon Linoff, Commented Apr 29, 2020 at 19:39
i want to replace with a blank..i'm working with sparksql. only regexp_replace is available. — satish g
– satish g, Commented Apr 29, 2020 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
– satish g, Commented Apr 29, 2020 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
– Talon, Commented Apr 29, 2020 at 19:57

kjsr7 · Accepted Answer · 2020-04-29 21:43:46Z

5

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

answered Apr 29, 2020 at 21:43

kjsr7

4275 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

REGEXP REPLACE with backslashes in Spark-SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related