regex - Finding string that has repeated pattern in snowflake

Question

I am trying to find the string that has repeated patterns in snowflake table. I am trying to get that using regex.

Example : String : 'abc' , 'abcabc' , 'snowsnowflake'

The Query return only " 'abcabc' , 'snowsnowflake' ". Because it has repeated patterns.

Thank you.

question from:https://stackoverflow.com/questions/65879708/finding-string-that-has-repeated-pattern-in-snowflake

Answer 1

I couldn't make it work with plain regex in SQL, but I was able to create a JavaScript UDF to get the desired results:

create or replace function find_repeated("x" string)
returns string
language javascript
as
$$
return x.match(/(.+)1/g)
$$;

select x.value
  , find_repeated(x.value)
  , find_repeated(x.value) is not null has_repeated
from table(split_to_table('abc,abcabc,snowsnowflake', ',')) x