SPLIT_PART for LDAP

Question

I am trying to understand if there is a way we can use SPLIT_PART in Snowflake that will break down the users from a LDAP Membership. If not SPLIT_PART, any other approaches?

The following exists as a single record which I want to split as three records extracting only the CN.

CN=John Doe,OU=Enabled Users,OU=User Accounts,DC=COM;CN=Mark Stu,OU=Enabled Users,OU=User Accounts,DC=COM;CN=Rachel Gail,OU=Enabled Users,OU=User Accounts,DC=COM

I wanted to split them as

• John Doe
• Mark Stu
• Rachel Gail

Answer 1

I think split_to_table may be a little easier, but if yours works either should be fine:

select substr(split("VALUE", ',')[0], 4) from TABLE_A, table(split_to_table(MEMBER, ';'));

This is assuming you have a table named TABLE_A with a column named MEMBER holding the LDAP strings.

Answer 2

I think I was able to get my answer but will mark a different approach as answer if someone posts a better code.

SELECT  SUBSTRING(c.value::string, CHARINDEX('CN=', c.value::string) + LEN('CN='), 
                    CHARINDEX(',OU=', c.value::string) - CHARINDEX('CN=', c.value::string) - LEN('CN=')) AS EMPLOYEE
FROM    Table_A,
LATERAL FLATTEN(input=>split(MEMBER, ';')) c
;