Search code examples
sqlsnowflake-cloud-data-platformregexp-substr

Please help me with regexp_substr in snowflake

In snowflake below is the regex for seperating strings based on >> but when data is with space it is not doing so I mean it is taking partial value not compete value

SELECT replace(REGEXP_SUBSTR( 'test1 >> test2 >> test3','([^>]*)([[:space:]]>>[[:space:]]|$)', 1,1 ) , ' >> ','') as test;

SELECT replace(REGEXP_SUBSTR( 'test1 >> test2 >> test3','\\w+([[:space:]]>>[[:space:]]|$)', 1,1) , ' >> ','') as test;

I have data like this 'test value >> test1 >> test2' But above regex is only giving as value but I want complete 'test value' can anyone help me on this?

Solution

  • SPLIT_PART is easier to use for this than regular expressions. Here's the example that was giving you trouble:

    WITH X AS
(SELECT 'test value >> test1 >> test2' as string_to_split)
SELECT 
SPLIT_PART(string_to_split, ' >> ', 1)
, SPLIT_PART(string_to_split, ' >> ', 2)
, SPLIT_PART(string_to_split, ' >> ', 3)
FROM X;