0

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
Julaayi
  • 403
  • 2
  • 8
  • 23

2 Answers2

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.

Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
  • Thanks Greg! Where are we passing the table name in your SELECT similar to what I have in my query? That is where I was confused when I looked at Snowflake documentation and couldn't use `split_to_table`. – Julaayi Nov 24 '20 at 22:35
  • In this case, I put the LDAP membership string in as a SQL variable. Based on your example, it looks as if the LDAP member a column in a table? I can adjust the SQL if so. – Greg Pavlik Nov 24 '20 at 22:39
  • Yes, it is a column in my table. If you could please do so, that would be great. – Julaayi Nov 24 '20 at 23:01
  • Updated to show how to use a column for an input instead of a string. – Greg Pavlik Nov 24 '20 at 23:12
  • Thank you! That worked. Marked your code as answer. Happy Thanksgiving! :) – Julaayi Nov 25 '20 at 02:42
0

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
;
Julaayi
  • 403
  • 2
  • 8
  • 23