Please help me with regexp_substr in snowflake

Question

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?

Answer 1

Why not use the SPLIT or SPLIT_PART functions? Using regex seems unnecessarily complex for what you are trying to achieve

Answer 2

I assume you want to split the string above and the separator is ">>"?

Then you can easily use

1. the split function to create an array with the different "parts" (already mentioned above) https://docs.snowflake.com/en/sql-reference/functions/split.html
2. the split_part function to receive a certain part (already mentioned above) https://docs.snowflake.com/en/sql-reference/functions/split_part.html
3. the split_to_table function https://docs.snowflake.com/en/sql-reference/functions/split_to_table.html

(Edit: just wanted to send you the references to the documentation)

Answer 3

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;