SELECT inputString, SUBSTRING( inputString, 1,42) + SUBSTRING( inputString, 105,16)
FROM (SELECT 'BUILTIN\ADMINISTRATORS: [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin]YES;[Database Creator]' AS inputString) Input

DECLARE @pString VARCHAR(MAX) = 'BUILTIN\ADMINISTRATORS: [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin]YES;[Database Creator];'
DECLARE @pDELIMITER CHAR(1) = ';'

;WITH SPLITS AS (
SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY t.N),
        Item       = SUBSTRING(@pString, t.N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, t.N) - t.N)
   FROM dbo.Tally as t
  WHERE t.N <= DATALENGTH(@pString)+1 --DATATLENGTH allows for trailing space delimiters
    AND SUBSTRING(@pDelimiter + @pString, t.N, 1) = @pDelimiter
)
SELECT Item
FROM SPLITS
WHERE ITEM Like '%YES'
ORDER BY ItemNumber 

SELECT inputString, SUBSTRING( inputString, 1,42) + SUBSTRING( inputString, 105,16)
FROM (SELECT 'BUILTIN\ADMINISTRATORS: [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin]YES;[Database Creator]' AS inputString) Input

NT AUTHORITY\SYSTEM:  [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin];[Database Creator]
NT SERVICE\MSSQL$instance1:  [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin];[Database Creator]
NT SERVICE\SQLAGENT$instance1:  [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin];[Database Creator]YES

NT AUTHORITY\SYSTEM:  [System Admin]YES;
NT SERVICE\MSSQL$instance1:  [System Admin]YES;
NT SERVICE\SQLAGENT$instance1:  [System Admin]YES;[Database Creator]YES;

SELECT inputString, SUBSTRING( inputString, 1,42) + SUBSTRING( inputString, 105,16)
FROM (SELECT 'NT SERVICE\SQLAGENT$instance1:  [System Admin]YES;[Security Admin];[Server Admin]YES;[setup Admin];[Process Admin];[Disk Admin]YES;[Database Creator]YES' AS inputString) Input

NT SERVICE\SQLAGENT$instance1:  [System Adess Admin];[Disk

DECLARE @pString VARCHAR(MAX) = 'BUILTIN\ADMINISTRATORS: [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin]YES;[Database Creator];'

SELECT Result=
    (SELECT item + CASE itemnumber WHEN 1 THEN ': ' ELSE ';' END
    FROM dbo.DelimitedSplit8K(REPLACE(@pString, ': ', ';'), ';')
    WHERE RIGHT(item, 3) = 'YES' OR itemnumber = 1
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)')

Declare @String Varchar(Max) = 'BUILTIN\ADMINISTRATORS: [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin]YES;[Database Creator]'
Select STUFF(STUFF(@String, 42, 62, ''), 59, 18, '')
Select Replace(REPLACE(@String, '[Security Admin];[Server Admin];[setup Admin];[Process Admin];', ''), '[Database Creator]', '')

vinu512 (9/5/2012)

---

You can use a lot of methods to do this. It all depends on what part of the String you want to remove, the type of requirement(one time or a lot of times) and a lot more conditions. Apart from the abovew mentioned ways you can use Stuff and Replace as well as follows:

Declare @String Varchar(Max) = 'BUILTIN\ADMINISTRATORS: [System Admin]YES;[Security Admin];[Server Admin];[setup Admin];[Process Admin];[Disk Admin]YES;[Database Creator]'
Select STUFF(STUFF(@String, 42, 62, ''), 59, 18, '')
Select Replace(REPLACE(@String, '[Security Admin];[Server Admin];[setup Admin];[Process Admin];', ''), '[Database Creator]', '')

Now, you have a lot of options and you can choose the best depending on the conditions that define your requirement.

Edit: The Stuff Function should give better performance. And I am sure when Dwain sees the Replace functions he would definitely come up with a couple of expert performance points. That would clear the situation a little more.

'BUILTIN\ADMINISTRATORS: [System Admin];[Security Admin];[Server Admin]YES;[setup Admin];[Process Admin]YES;[Disk Admin];[Database Creator]YES'