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. 🙂
I think what the OP wants to do is return any roles that have a YES next to them. My assumption was that any of the listed roles could be YES or ''. What if the string was:
'BUILTIN\ADMINISTRATORS: [System Admin];[Security Admin];[Server Admin]YES;[setup Admin];[Process Admin]YES;[Disk Admin];[Database Creator]YES'
What would your code return?
I don't think he was looking for something that is as specific as yours (but of course I could be wrong).
As to performance, applying a binary collation to @pString (my query) would be my only suggestion. Since he didn't provide a TABLE with a column of such strings to parse, I wasn't aiming for high performance.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St