CODEWITHSUNDEEP
regexamazon-redshift
tiomno
tiomno

Reputation: 2228

Matching consecutive digits REGEXP_REPLACE in Redshift

I'm trying to remove consecutive numbers from a string in Redshift.

From '16,16,16,3,3,4,16,16,' I want to get '16,3,4,16,'.

The following construction doesn't work for me:

SELECT regexp_replace('16,16,16,3,3,4,16,16,', '(.+)\1{1,}', '\1');

It's returning exactly the same string. :(

Thanks!

Upvotes: 2

Views: 368

Answers (2)

Joe Harris
Joe Harris

Reputation: 14035

Here you go. 😀

SELECT regexp_replace('16,16,16,3,3,4,16,16,', '([0-9]+,){2}', '$1') ;

 regexp_replace
----------------
 16,3,4,16,

Upvotes: 1

Jon Scott
Jon Scott

Reputation: 4354

Here is the answer using a Redshift python UDF.

create or replace function dedupstring(InputStr varChar)
  returns varchar
stable
as $$
    OutputStr=''
    PrevStr=''
    first=True
    for part in InputStr.split(','):
        if part <> PrevStr:
            if first:
                OutputStr+=part
            else:
                OutputStr+=','+part
            PrevStr=part
            first=False
    return OutputStr
$$ language plpythonu;

Select dedupstring('16,16,16,3,3,4,16,16,');

This returns '16,3,4,16,'

Upvotes: 1

Related Questions