locked
Horrible data - need a way to clean RRS feed

  • Question

  • Hi All

    Please find my table insertion script:

    CREATE TABLE [dbo].[TestUserAccounts](
    	[Full_Username] [varchar](255) NULL,
    	[TestPerms] [varchar](max) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    INSERT [dbo].[TestUserAccounts] ([Full_Username], [TestPerms]) VALUES (N'MMI\dmorris', N'Domain Users   (Users/internal.msilm.com);  FS_SQLBackup_Deside_RW   (File Server Access/MSILM Groups/FWP/internal.msilm.com);  L4 IT   (MSILM Groups/z_NewOrder/internal.msilm.com);  mmishrpnt01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  mmishrpnt01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  mmishrpnt02 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  mmishrpnt02 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  mmisvr15 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  mmisvr18 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  mmisvr18_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  mmisvr8 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-ADM-F01_AgressoArchive_RO   (File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  MSILM-ADM-F01_AgressoArchive_RW   (File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  MSILM-AGR-A01_DataFiles_RW   (File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  MSILM-AGR-APP01 Data Files Share RW Access Group   (File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agr-app01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-AGR-D01_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-AGR-D01_sqlrw   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-agrd-a01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-agr-db01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-agr-db01_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-agresso04 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-agresso04_Data Files Share RO Access   (File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agresso04_Data Files Share RW Access   (File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agresso05_Data Files_agress_Data Import RW all Sub Folders   (Server Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agresso06 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-agresso07 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-agresso07_Data Files Share RO Access   (Agresso/IS/File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agresso07_NonParsedFiles Share RO Access   (Agresso/IS/File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agrpt-a01 Data Files Share RO Access   (Server Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agrpt-a01 Data Files Share RW Access   (Server Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agrpt-a01_Data Files_agress_Data Import RW all Sub Folders   (Server Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agrst-a01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-AGRST-A01_Data Files Share Access   (MSILM Groups/z_NewOrder/internal.msilm.com);  MSILM-AGRST-A01_Data Files_AGR_Data Import RW all Sub Folders   (Server Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  MSILM-AGRU-A01 Data Files Share RW Access   (Server Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-agru-a01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-appsvr02 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bi-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bi-a01_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bid-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bid-a01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bid-a02 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bid-a02 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bidev01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bidev01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bidev01_bi_environments RO   (File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-bidev01_FileTransfer RO   (File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-bi-dr administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bi-dr rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-BI-DR_BenchmarkPremiumsRO   (BI/Database Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-BI-DR_bi_managedRO   (BI/Database Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-BI-DR_bi_stageRO   (BI/Database Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-BI-DR_finance_monthend_ddsRO   (BI/Database Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-BI-DR_finance_monthend_repRO   (BI/Database Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-BI-DR_Production_ddsRO   (BI/Database Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-BI-DR_Production_QE__ddsRO   (BI/Database Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-BI-DR_Production_QE__repRO   (BI/Database Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-BI-DR_production_repRO   (BI/Database Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bipp-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bitest01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bitest01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bitest02 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bitest02 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bitest03 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-bitest03 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-carsapp01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-carsapp01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-CDS-D01_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-CSM-D01_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-ctxdata01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-CTXDS-D01_SQLAdmin   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-ctxrec01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-ctxtest02 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DC03_ADReports_RW   (File Access Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-dev-cm01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dev-cm01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dm-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dm-a01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dmd-a01 Server Access   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dmpp-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dmpp-a01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dmpt-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dmpt-a01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dmst-a01 Server Access   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dmu-a01 Server Access   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-P-A01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-P-A01_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dw-p-a01_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-P-A02 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-P-A02_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-dw-p-a02_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-PP-A01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-PP-A01_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-PP-A02 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-PP-A02_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-PP-A03 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-U-A01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-U-A02 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-DW-U-A02_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-EII-A01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-EII-A02 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-EII-D01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-eii-d01_sqladmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-EII-D02 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-eii-d02_ro_dd_sql_access   (Application Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-eii-d02_sqladmin   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-eii-d02_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-EII-P-A01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-EII-P-D01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-eii-p-d01_ro_dd_sql_access   (Application Groups/MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-eii-p-d01_sqladmin   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-EII-P-D02_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-EII-T-D01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-erc-db02 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-erc-db02 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-erc-db02_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-erc-db03 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-ev-d01_sqladmin   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-evda01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-file01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-file01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-GFE-A01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-gis-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-gis-d01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-gis-d01_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-igloo rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-igloo-00 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-igloo-01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-igloo-02 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-igloo-02 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-igloo-03 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-igloo-04 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-labfile01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-lbl-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-lbl-a01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-lblpp-a01 Server Access   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-lnxtest01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-lnxtest01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-lnxuat01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-lnxuat01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-mossd-01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-mossd-01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-msmdr01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-msmdr01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-msmdr01_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-msp-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-msp-d01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-msp-p-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-msp-p-d01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-mwr01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-ox-a01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-ox-a01_sqladmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-ox-a01_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-oxd-a01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-oxd-a01_sqladmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-OX-PP-A01 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-OX-PP-A01_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sccm-a01 administrators   (SCCM/Application Service Accounts/IT Service Accounts/IT Service/internal.msilm.com);  msilm-sccm-a01_sqladmins   (SCCM/Application Service Accounts/IT Service Accounts/IT Service/internal.msilm.com);  msilm-shp-d-d01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-shp-d-w01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-shp-d-w02 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-shp-p-d01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-SHP-P-D01_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-shp-p-d01_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-shp-p-w01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-shp-p-w02 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-soa-p-d01_sqladmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-SQL01 TQS Australia Database Access Group   (MSILM Groups/z_NewOrder/internal.msilm.com);  MSILM-SQL01 TQS Database Access Group   (MSILM Groups/z_NewOrder/internal.msilm.com);  MSILM-SQL01 TQS Test Database Access Group   (MSILM Groups/z_NewOrder/internal.msilm.com);  msilm-sql02 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql02 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql02_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-SQL03 Administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  MSILM-SQL03_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sqlbup01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql-d01 administrators   (Administrator Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql-d01 rdp users   (Remote Desktop Access Groups/Server Security Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql-d01_sqlro   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql-d02_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql-d03_SQLAdmins   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com);  msilm-sql-d03_TQS_Access   (SQL Access Groups/IT Service Groups/IT Service/internal.msilm.com)')
    GO
    
    I am having major problems parsing this sql ; seperated columnt out to something more legible

    I have two requirements:

    1) Seperator is a ; so i need to create a new row of information every time we get a ; - convert rows into a column


    2) I want to strip from the results all text between the brackets - but ONLY if there is a "/" detected between them.

    For example
    "Domain Users   (Users/internal.msilm.com)" -  to become just "Domain Users"

    3) Unfortunately some legitimate groups also have brackets - if we don't detect a "/" between the brackets i would like to leave that in the description

    For example:

    (2010) IE Security (Users/internal.msilm.com) to become "(2010) IE Security" - as there was no / between the first 2 brackets.

    is such a feat possible? i've been really struggling with this one, any help would be greatly appreciated


    Wednesday, October 10, 2018 12:16 PM

Answers

  • -- swePeso 
    WITH cteData(UserName, Content)
    AS (
    	SELECT		tau.Full_Username AS UserName,
    			LTRIM(tp.x.value('(.)', 'VARCHAR(MAX)')) AS Content
    	FROM		@TestUserAccounts AS tau
    	CROSS APPLY	(
    				VALUES	(CAST('<t>' + REPLACE(tau.TestPerms, ';', '</t><t>') + '</t>' AS XML))
    			) AS d(x)
    	CROSS APPLY	d.x.nodes('(t)') AS tp(x)
    )
    SELECT	UserName,
    	Content,
    	RTRIM(SUBSTRING(Content, 1, CHARINDEX(' (', Content + ' (') - 1)) AS GroupName
    FROM	cteData;


    N 56°04'39.26"
    E 12°55'05.63"

    • Marked as answer by James OHara Wednesday, October 10, 2018 3:10 PM
    Wednesday, October 10, 2018 12:51 PM

All replies

  • DECLARE @str VARCHAR(100)='(2010) IE Security (Users/internal.msilm.com)'


    ;with cte
    as
    (
    SELECT row_number () over (order by (select 0)) rn, value FROM  string_split(@str,' ')
    )  select stuff(
      (select ' ' + value as [text()] from cte 
      where (value not like '(%' or rn<=1) and value >''
     order by rn for xml path('')),
      1, 1, '')
      

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 10, 2018 12:29 PM
    Answerer
  • Hi Uri

    Thanks for your help

    Apparently i don't have a function called String_Split - this one is a SQL 2012 so not sure if thats a newer function?

    J

    Wednesday, October 10, 2018 12:31 PM
  • CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
    RETURNS table
    /* Use Option(MaxRecursion 0) in queries that call this function if 
       there can be more than 99 delimited values in @DelimitedString */
    AS
    RETURN (
        WITH Pieces (ID, start, stop) AS (
          SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
          UNION ALL
          SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
          FROM Pieces
          WHERE stop > 0
        )
        SELECT ID,
          SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
        FROM Pieces
      )
    GO

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 10, 2018 12:34 PM
    Answerer
  • -- swePeso
    WITH cteData(UserName, Content)
    AS (
    	SELECT		tau.Full_Username AS UserName,
    			LTRIM(tp.value) AS Content
    	FROM		dbo.TestUserAccounts AS tau
    	CROSS APPLY	STRING_SPLIT(tau.TestPerms, ';') AS tp
    )
    SELECT	UserName,
    	Content,
    	RTRIM(SUBSTRING(Content, 1, CHARINDEX(' (', Content + ' (') - 1)) AS GroupName
    FROM	cteData;


    N 56°04'39.26"
    E 12°55'05.63"

    Wednesday, October 10, 2018 12:39 PM
  • Hi THere

    Thats  great - how do i use this with my table in the example  given?

    Thanks


    James

    I ran it on my table and it says:

    Msg 512, Level 16, State 1, Line 2
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    (1 row(s) affected)

    The table has 10,000 entries so it needs to work on the whole table

    Need to return all usernames with their splits

    This is very frustrating

    Regards

    J


    • Edited by James OHara Wednesday, October 10, 2018 12:55 PM
    Wednesday, October 10, 2018 12:40 PM
  • Use CROSS APPLY operator like Peter showed you 

    WITH cte

    AS

    (

    SELECT * ,ROW_NUMBER () OVER (ORDER BY (SELECT 0)) rn FROM tbl

    CROSS APPLY dbo.Split (colname,';')  as c

     select stuff(
      (select ' ' + value as [text()] from cte 
      where (value not like '(%' or rn<=1) and value >''
     order by rn for xml path('')),
      1, 1, '')


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, October 10, 2018 1:02 PM
    Answerer
  • Hi

    This worked on the testrow i had until i added the rest of my dataset back in - it seems to be getting tripped out

    Please could you try adding another row of data to the set and re-run it

    INSERT [dbo].[TestUserAccounts] ([Full_Username], [TestPerms]) VALUES (N'MLPNET\SYSLPA', N'2008_Subscribe_Live   (XPApps/amlin.com);  ACHERON Local Administrators   (Server_Admins/Security Groups/amlin.com);  ACI Support   (Security Groups/amlin.com);  All Amlin Users   (Distribution Lists/Users/amlin.com);  All At Amlin (Group)   (Distribution Lists/Users/amlin.com);  All at Amlin (St Helen''s)   (Distribution Lists/Users/amlin.com);  AmlinMIT   (Security Groups/amlin.com);  App_Claws_IE   (App Groups/amlin.com);  App_CroweLivestock_Development_DBUsers   (Development/App_CroweLivestock/App Groups/amlin.com);  App_CroweLivestock_Production_DBUsers   (Production/App_CroweLivestock/App Groups/amlin.com);  App_install_Rights   (XPApps/amlin.com);  App_XPAmlinMIS   (XPApps/amlin.com);  ASG SystemMail - Mailbox Access   (Shared Mailboxes/Security Groups/amlin.com);  ATLAS Local Administrators   (Server_Admins/Security Groups/amlin.com);  Atual   (Security Groups/amlin.com);  AURORA Local Administrators   (Server_Admins/Security Groups/amlin.com);  BIAST   (Security Groups/amlin.com);  CE Lloyds Amlin MIT RDP Access   (Security Groups/amlin.com);  Citrix Farm Users   (Citrix Application Groups/Citrix Environment/amlin.com);  CITRIX_ACI_MIGE2E_ENV_Pipe_Cleaners   (Security Groups/amlin.com);  CITRIX_ACI_MIGE2E_ENV_SSA_Users   (Security Groups/amlin.com);  CITRIX_ACI_PRD_TST_SSA_Users   (Security Groups/amlin.com);  CITRIX_ACI_PRD_TST_Testers   (Security Groups/amlin.com);  CITRIX_ACI_TINT_ENV_SSA_Users   (Security Groups/amlin.com);  Citrix_AmlinDBA_Tools   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_BoardClient   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_Desktop   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_Elgar   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_ExactBMRC   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_Fdrive   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_FINT_TD   (ACI/Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_HDrive   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_IE   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_LaptopUsers   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_Mig_E2E   (ACI/Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_MIS   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_MsAccess   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_MsExcel   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_MSOffice2010   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_MsOutlook   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_MsWord   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_My Desktop   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_PC_Anywhere   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_Powerpoint   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_RDP   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_S2000   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_S2000_UAT   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_S2000AE_PreProd   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_S2000BM   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_S2000BM_RC   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_S2000BM_UAT   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_SSA2000   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_Subscribe_SSS   (Security Groups/amlin.com);  Citrix_SubscribeAG   (Security Groups/amlin.com);  Citrix_SubscribeLondon_PPM   (Security Groups/amlin.com);  Citrix_TINT   (ACI/Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_Users   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_Voyager   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_Voyager_Designer   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_VoyagerAPlus_Designer   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_VoyagerDBA_Tools   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_VoyagerDEV_UAT   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_VoyagerHAVENKJ_Live   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_VoyagerHAVENKJ_UAT   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_VoyagerHavenKJ_UAT_ITSClientDesigner   (Citrix Application Groups/Citrix Environment/amlin.com);  Citrix_VoyagerTraining   (Citrix Application Groups/Citrix Environment/amlin.com);  D Apps Doc RW   (Security Groups/amlin.com);  D Apps RW   (Security Groups/amlin.com);  D HAVENKJ_HKJACA_Marine Trade_Live_Application   (Security Groups/amlin.com);  D MARINE DIVISION RW   (Security Groups/amlin.com);  D_Managing Agency_AUL Compliance 010109_DSAR_RW   (Security Groups/amlin.com);  DirectAccess Users   (Windows 7/Security Groups/amlin.com);  Domain Citrix System   (Security Groups/amlin.com);  Domain Users   (Security Groups/amlin.com);  Dum_S2000TestV26123   (App Groups/amlin.com);  DW2DBAmlinDWUsers   (Security Groups/amlin.com);  EDS Amlin   (Security Groups/amlin.com);  ElevatedDesktopUsers   (Windows 7/Security Groups/amlin.com);  Elgar Users   (Security Groups/amlin.com);  G Checkpoint FDE Deployment   (Security Groups/amlin.com);  G Crowe Livestock RW   (Security Groups/amlin.com);  G havenkj haven IT support docs R   (Security Groups/amlin.com);  G MGA Bus Proj 02 P24 RW   (Security Groups/amlin.com);  G MGA GOO ACI PRP RW   (Security Groups/amlin.com);  G minerva ROOMScriptMg RW   (Security Groups/amlin.com);  G Minerva ROOMSupport RW   (Security Groups/amlin.com);  G MngngAgncy LndnHR RW   (Security Groups/amlin.com);  G Sys Dep Info Sec App Audits RW   (Security Groups/amlin.com);  G Sys Dep MAN 12 Perf Room Cases   (Security Groups/amlin.com);  G_Elgar6_Libraries_RW   (Security Groups/amlin.com);  G_XenApp_PreProd_Subscribe_Admin   (Citrix Application Groups/Citrix Environment/amlin.com);  G_XenApp_Prod_Subscribe   (Citrix Application Groups/Citrix Environment/amlin.com);  G_XenApp_Prod_Subscribe_Admin   (Citrix Application Groups/Citrix Environment/amlin.com);  G-Citrix-PRD-ActurisAssistantV7PROD   (Security Groups/amlin.com);  G-Citrix-PRD-Crowe   (Security Groups/amlin.com);  G-Citrix-PRD-ExcelforClawsBM   (Security Groups/amlin.com);  G-Citrix-PRD-ExcelforWASPBM   (Security Groups/amlin.com);  G-Citrix-PRD-F_Drive   (Security Groups/amlin.com);  G-Citrix-PRD-H_Drive   (Security Groups/amlin.com);  G-Citrix-PRD-My_Desktop   (Security Groups/amlin.com);  G-Citrix-PRD-OutlookforCLAWS   (Security Groups/amlin.com);  G-Citrix-PRD-OutlookforCLAWSBM   (Security Groups/amlin.com);  G-Citrix-PRD-OutlookforWASPBM   (Security Groups/amlin.com);  G-Citrix-PRD-RDP   (Security Groups/amlin.com);  G-Citrix-PRD-Subscribe_AE_Admin   (Security Groups/amlin.com);  G-Citrix-PRD-Subscribe_AG_Admin   (Security Groups/amlin.com);  G-Citrix-PRD-Subscribe_London_Admin   (Security Groups/amlin.com);  G-Citrix-PRD-ThunderHeadAdmin   (Security Groups/amlin.com);  G-Citrix-PRD-ThunderheadBusContStudio   (Security Groups/amlin.com);  G-Citrix-PRD-ThunderheadBusObjStudio   (Security Groups/amlin.com);  Haven   (Security Groups/amlin.com);  HBC_Aronova_RW   (Security Groups/amlin.com);  HBD   (Security Groups/amlin.com);  Intranet Dev   (Security Groups/amlin.com);  IntranetDomainName   (Security Groups/amlin.com);  IPass_Systems   (Security Groups/amlin.com);  IronPort EDS Users   (Security Groups/amlin.com);  IronPort Pilot Users   (Security Groups/amlin.com);  IS_Apps_Amlin Risk Registration   (Security Groups/amlin.com);  ITAR-AmlinMIT   (Security Groups/amlin.com);  KJC   (Security Groups/amlin.com);  KJC Managing Agency   (Security Groups/amlin.com);  KJC-Amlin Marine Services-RW   (Security Groups/amlin.com);  KnowledgeCenter_Office-AddIn_INT_SUP   (Windows 7/Security Groups/amlin.com);  KnowledgeCenter_Office-AddIn_PROD_SUP   (Windows 7/Security Groups/amlin.com);  Limit Exception Authority   (Security Groups/amlin.com);  Limit Exeption Authrity-DLG   (Security Groups/amlin.com);  Limit UWR Exception Authority   (Security Groups/amlin.com);  Lumension Exception Group Read Write   (Lumension Device Control/Security Groups/amlin.com);  Masteruser Admin   (Security Groups/amlin.com);  MGA Blueprint Change   (Security Groups/amlin.com);  MIS Users   (Security Groups/amlin.com);  MLP01   (Security Groups/amlin.com);  MLP03   (Security Groups/amlin.com);  MLP04   (Security Groups/amlin.com);  MLP07   (Security Groups/amlin.com);  NP-Elgar-Users   (Security Groups/amlin.com);  PaperCut Users   (Windows 7/Security Groups/amlin.com);  PaperCut voygap   (Windows 7/Security Groups/amlin.com);  PHOBOS Local Administrators   (Server_Admins/Security Groups/amlin.com);  PR-Elgar-Users   (Security Groups/amlin.com);  Profile Cleaner Test Users   (Security Groups/amlin.com);  Project Dolphin DB SC&RJ1   (Security Groups/amlin.com);  SDCStandard   (App Groups/amlin.com);  Sg-dl-prd-AzureSync   (Azure/Security Groups/amlin.com);  SSUBSS0001 DMZ RDP Access   (Security Groups/amlin.com);  STM   (Security Groups/amlin.com);  StMargarets_Users   (Security Groups/amlin.com);  STSEA0001 Remote Desktop Users   (Server_Admins/Security Groups/amlin.com);  STSEA0016 Local Administrators   (Server_Admins/Security Groups/amlin.com);  SXENAS0021 Local Administrators   (Server_Admins/Security Groups/amlin.com);  SYS   (Security Groups/amlin.com);  SYS ROOM   (Security Groups/amlin.com);  SYS_ELGAR5COPY_RW   (Security Groups/amlin.com);  TeamTrack_Admins   (Security Groups/amlin.com);  Telephone List   (Security Groups/amlin.com);  TFS$CLAWSDevelopers   (TFS/Security Groups/amlin.com);  TFS$U&C   (TFS/Security Groups/amlin.com);  TFS$WASPDevelopers   (TFS/Security Groups/amlin.com);  UDCAdminGroup   (Security Groups/amlin.com);  Unknown usage - AVN_Group Reinsurance   (Security Groups/amlin.com);  Webmail   (Security Groups/amlin.com);  Windows7FolderRedirection   (Windows 7/Security Groups/amlin.com);  XPApp_AccountEN   (XPApps/amlin.com);  XPApp_BDEAdmin   (XPApps/amlin.com);  XPApp_Exact   (XPApps/amlin.com);  XPApp_FMS   (XPApps/amlin.com);  XPApp_Lors   (XPApps/amlin.com);  XPApp_Resq   (XPApps/amlin.com);  XPApp_SubscribeRC   (XPApps/amlin.com);  XPApp_SubscribeUAT   (XPApps/amlin.com);  XPApp_SwordFish_Macros   (XPApps/amlin.com);  XPApp_Sybase12   (XPApps/amlin.com);  XPDum_AcrobatReader6.01   (XPApps/amlin.com);  XPDum_SQL   (XPApps/amlin.com);  XPDum_UninstallGoogleEarth   (XPApps/amlin.com);  XPDum_WASP   (XPApps/amlin.com)')
    

    The error i get is:

    Msg 9411, Level 16, State 1, Line 1
    XML parsing: line 1, character 9956, semicolon expected

    I opened that field in notepad++ and checked - char 9956 is a forwardslash:

    "TFS$WASPDevelopers   (TFS/Security Groups"

    The $ couldn't be doing something wierd could it? I tried doing a string replace on the $ makes position 9956 the c in security so that doesnt fix it

    Thanks alot


    James

    Wednesday, October 10, 2018 2:55 PM
  • Wow have to say this worked! all i had to do was remove the invalid ampersands from the source data!

    thankyou so much this has made my life MUCH easier and you have been amazing!

    upvoted answer

    Wednesday, October 10, 2018 3:11 PM