1

I have a function to replace different types of characters with nothing like below.

CAST(TRANSLATE(REGEXP_REPLACE(UPPER(number),' |,|-|/|&|_|''''',''),'.|(|)','') as string) as number

Everything is working fine, except the ''''' part, where i want to replace single quote/apostrophe with nothing.

Example:

TYLER'SCOFFEE should come out as TYLERSCOFFEE

I checked examples from this forum and it seems we can use a backslash to acheive the above, but when i use backslash like below, it throws an error.

select CAST(TRANSLATE(REGEXP_REPLACE('TYLER\'SCOFFEE',' |,|-|/|&|_|'\'',''),'.|(|)','') as string) as number;
Error: Error while compiling statement: FAILED: ParseException line 1:68 character '\' not supported here (state=42000,code=40000)

Can someone help me on how to solve my problem? Thanks.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
jahan
  • 103
  • 4
  • 19

1 Answers1

2

Use doble-quoted regexp. Inside double-quotes, single-quote is shielded:

select 'TYLER\'SCOFFEE' as original_str, regexp_replace('TYLER\'SCOFFEE',"'",'') result;

Result:

original_str    result
TYLER'SCOFFEE   TYLERSCOFFEE

Also if you need to replace many different characters with the same replacement, put all of them into [], no need to use pipe |:

select regexp_replace('TYLER\'SCOFFEE ,-/&_', 
                      "[ ,-/&_']", 
                      '')

Result:

TYLERSCOFFEE
leftjoin
  • 36,950
  • 8
  • 57
  • 116