String replace

• sqlfriend

  SSC Guru

  Points: 52794

  More actions

  August 5, 2015 at 5:46 pm

  #306401

  I am pulling User and their groups from AD to a csv file. Then import them into SQL table, but I would like some clean up of the filed of Groupnames.

  The user could be in one group or multiple groups.

  For example a user Jsmith are members of two groups. It shows in the Groupname like below

  CN=ssd_vpn_2015,CN=Users,DC=myorg,DC=org;CN=SSC_PWR_SCHOOL,CN=Users,DC=myorg, DC=org

  Now I would like to replace this string with only groupnames, no cn, dc etc. and separated by semicolumn.(right now the group is separated by semicolon)

  For example I like it to be:

  ssd_vpn_2015;SSC_PWR_SCHOOL

  Any string function or regular expression that can be used to replace or remove those unnecessary characters and how to do that?

  Thanks

• WayneS

  SSC Guru

  Points: 95461

  More actions

  August 5, 2015 at 6:01 pm

  #1817733

  sqlfriends (8/5/2015)

  ---

  I am pulling User and their groups from AD to a csv file. Then import them into SQL table, but I would like some clean up of the filed of Groupnames.

  The user could be in one group or multiple groups.

  For example a user Jsmith are members of two groups. It shows in the Groupname like below

  CN=ssd_vpn_2015,CN=Users,DC=myorg,DC=org;CN=SSC_PWR_SCHOOL,CN=Users,DC=myorg, DC=org

  Now I would like to replace this string with only groupnames, no cn, dc etc. and separated by semicolumn.(right now the group is separated by semicolon)

  For example I like it to be:

  ssd_vpn_2015;SSC_PWR_SCHOOL

  Any string function or regular expression that can be used to replace or remove those unnecessary characters and how to do that?

  Thanks

  DECLARE @s VARCHAR(100) = 'CN=ssd_vpn_2015,CN=Users,DC=myorg,DC=org;CN=SSC_PWR_SCHOOL,CN=Users,DC=myorg, DC=org';

  WITH cte AS

  (

  SELECT ds1.ItemNumber, ds1.Item, ds2.ItemNumber AS IN2, ds2.Item AS I2,

  MAX(CASE WHEN ds3.ItemNumber = 1 THEN ds3.Item ELSE NULL END) ds3Col1,

  MAX(CASE WHEN ds3.ItemNumber = 2 THEN ds3.Item ELSE NULL END) ds3Col2

  FROM dbo.DelimitedSplit8K(@s, ';') ds1

  CROSS APPLY dbo.DelimitedSplit8K(ds1.Item, ',') ds2

  CROSS APPLY dbo.DelimitedSplit8K(ds2.Item, '=') ds3

  GROUP BY ds1.ItemNumber, ds1.Item, ds2.ItemNumber, ds2.Item

  )

  SELECT STUFF((

  SELECT ',' + ds3Col2

  FROM cte

  WHERE RTRIM(LTRIM(ds3Col1)) <> 'DC'

  AND ds3Col2 != 'Users'

  ORDER BY cte.ds3Col1

  FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'),1,1,'')

  See the link in my signature for Splitting Delimited Strings, and see my article at Creating a comma-separated list[/url] for making the comma-separated list.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2