用Hive中的任何内容替换单引号(') [英] Replace single quote(') with nothing in Hive

问题描述

我有一个功能可以替换不同类型的字符，如下所示.

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.

示例:

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.

推荐答案

使用带引号的正则表达式.在双引号内，单引号被屏蔽:

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

select 'TYLER\'SCOFFEE' as original_str, regexp_replace('TYLER\'SCOFFEE',"'",'') 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 ,-/&_', 
                      "[ ,-/&_']", 
                      '')

结果:

TYLERSCOFFEE

这篇关于用Hive中的任何内容替换单引号(')的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！