hive - REPLACE() function for HiveSQL, REGEXP_REPLACE() not working as intended

Question

I run the following statement to replace the characters of ".." to ".":

CREATE TABLE TableA AS
SELECT Column1,
REGEXP_REPLACE(Column2, "..", ".") AS NewColumn
FROM TableB;

The result of NewColumn became ".......", what's wrong with the REGEXP_REPLACE() function?

question from:https://stackoverflow.com/questions/65952959/replace-function-for-hivesql-regexp-replace-not-working-as-intended

Answer 1

In addition to what @mck proposed, you can use quantifier for repeating patterns

REGEXP_REPLACE(Column2, "\.{2}", ".")

Or if you want to replace 2 or more dots with single one:

REGEXP_REPLACE(Column2, "\.{2,}", ".")