string manipulation - Need help !

DECLARE @Test Table(
MyString varchar(MAX))
INSERT @Test VALUES
('NT AUTHORITY\SYSTEM: [System Admin]YES;[Security Admin];[Server Admin]YES;[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')

SELECT LEFT( MyString, CHARINDEX( ':', MyString) + 1) +
CASE WHEN CHARINDEX( 'System Admin]YES;', MyString) > 0
THEN '[System Admin]YES;' ELSE '' END +
CASE WHEN CHARINDEX( 'Security Admin]YES;', MyString) > 0
THEN '[Security Admin]YES;' ELSE '' END +
CASE WHEN CHARINDEX( 'Server Admin]YES;', MyString) > 0
THEN '[Server Admin]YES;' ELSE '' END +
CASE WHEN CHARINDEX( 'setup Admin]YES;', MyString) > 0
THEN '[setup Admin]YES;' ELSE '' END +
CASE WHEN CHARINDEX( 'Process Admin]YES;', MyString) > 0
THEN '[Process Admin]YES;' ELSE '' END +
CASE WHEN CHARINDEX( 'Disk Admin]YES;', MyString) > 0
THEN '[Disk Admin]YES;' ELSE '' END +
CASE WHEN CHARINDEX( 'Database Creator]YES', MyString) > 0
THEN '[Database Creator]YES;' ELSE '' END
FROM @Test

Luis Cazares (9/5/2012)

It might not be the optimal way, but it's simple enough for everyone to understand. However, I haven't tested for performance against other methods.

DECLARE @Test	Table(
	MyString	varchar(MAX))
INSERT @Test VALUES
('NT AUTHORITY\SYSTEM:  [System Admin]YES;[Security Admin];[Server Admin]YES;[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')

SELECT LEFT( MyString, CHARINDEX( ':', MyString) + 1) +
	CASE WHEN CHARINDEX( 'System Admin]YES;', MyString)  > 0 
		THEN '[System Admin]YES;' ELSE '' END +
	CASE WHEN CHARINDEX( 'Security Admin]YES;', MyString)  > 0 
		THEN '[Security Admin]YES;' ELSE '' END +
	CASE WHEN CHARINDEX( 'Server Admin]YES;', MyString)  > 0 
		THEN '[Server Admin]YES;' ELSE '' END +
	CASE WHEN CHARINDEX( 'setup Admin]YES;', MyString)  > 0 
		THEN '[setup Admin]YES;' ELSE '' END +
	CASE WHEN CHARINDEX( 'Process Admin]YES;', MyString)  > 0 
		THEN '[Process Admin]YES;' ELSE '' END +
	CASE WHEN CHARINDEX( 'Disk Admin]YES;', MyString)  > 0 
		THEN '[Disk Admin]YES;' ELSE '' END +
	CASE WHEN CHARINDEX( 'Database Creator]YES', MyString)  > 0 
		THEN '[Database Creator]YES;' ELSE '' END
FROM @Test