sqlsnowflake-cloud-data-platformsnowsql

SPLIT_PART for LDAP

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

Solution

  • 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.