My fellow blogger Ted Kreuger posted [this powershell thing][1] where he wants to remove the NOLOCk keyword from a statement (I have no idea why but he must have a good reason). So I commented that he could perhaps have done it with linq instead of regex. I was mistaken linq has nothing to do with this.
Regex is evil.
So I set up a testcase, I didn’t feel like doing it in but in csharp, just for fun.
First an interface.
namespace NoLock
{
public interface INoLock
{
string FindAndReplaceNoLock(string noLock);
}
}```
Then some tests for the regex.
```csharp
using NUnit.Framework;
namespace NoLock
{
/// <summary>
/// TestNoLockLinq
/// </summary>
/// <remarks> </remarks>
[TestFixture]
public class TestNoLockRegex
{
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithInTheBeginning
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithInTheBeginning()
{
var nolock = new NoLockRegEx();
Assert.AreEqual("SELECT * FROM HumanResources.Department WITH (INDEX(AK_Department_Name))",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department WITH (NOLOCK,INDEX(AK_Department_Name))"));
}
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithAtTheEnd
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithAtTheEnd()
{
var nolock = new NoLockRegEx();
Assert.AreEqual("SELECT * FROM HumanResources.Department WITH (INDEX(AK_Department_Name))",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department WITH (INDEX(AK_Department_Name),NOLOCK)"));
}
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithAlone
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithAlone()
{
var nolock = new NoLockRegEx();
Assert.AreEqual("SELECT * FROM HumanResources.Department",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department WITH (NOLOCK)"));
}
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsAtTheEndNoParenthesis
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsAtTheEndNoParenthesis()
{
var nolock = new NoLockRegEx();
Assert.AreEqual("SELECT * FROM HumanResources.Department",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department NOLOCK"));
}
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsAtTheEndWithParenthesis
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsAtTheEndWithParenthesis()
{
var nolock = new NoLockRegEx();
Assert.AreEqual("SELECT * FROM HumanResources.Department",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department (NOLOCK)"));
}
}
}```
All red.
then Teds implementation.
```csharp
using System;
using System.Text.RegularExpressions;
namespace NoLock
{
public class NoLockRegEx: INoLock
{
public string FindAndReplaceNoLock(string noLock)
{
if (Regex.IsMatch(noLock, "((\({0,1}NOLOCK\,)|(\,NOLOCK\({0,1}))", RegexOptions.IgnoreCase))
{
var replace = Regex.Replace(noLock, "((NOLOCK\,)|(\,NOLOCK\({0,1}))", "");
return replace.Trim();
}
else if(Regex.IsMatch(noLock, "(WITH\s(\({0,1}NOLOCK\){0,1})|\({0,1}NOLOCK\){0,1})", RegexOptions.IgnoreCase))
{
var replace = Regex.Replace(noLock, "(WITH\s(\({0,1}NOLOCK\){0,1})|\({0,1}NOLOCK\){0,1})", "");
return replace.Trim();
}
return "";
}
}
}```
All green.
Then tests for the alternativee version.
```csharp
using NUnit.Framework;
namespace NoLock
{
/// <summary>
/// TestNoLockLinq
/// </summary>
/// <remarks> </remarks>
[TestFixture]
public class TestNoLockAlt
{
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithInTheBeginning
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithInTheBeginning()
{
var nolock = new NoLockAlt();
Assert.AreEqual("SELECT * FROM HumanResources.Department WITH (INDEX(AK_Department_Name))",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department WITH (NOLOCK,INDEX(AK_Department_Name))"));
}
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithAtTheEnd
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithAtTheEnd()
{
var nolock = new NoLockAlt();
Assert.AreEqual("SELECT * FROM HumanResources.Department WITH (INDEX(AK_Department_Name))",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department WITH (INDEX(AK_Department_Name),NOLOCK)"));
}
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithAlone
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsHidinginWithAlone()
{
var nolock = new NoLockAlt();
Assert.AreEqual("SELECT * FROM HumanResources.Department",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department WITH (NOLOCK)"));
}
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsAtTheEndNoParenthesis
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsAtTheEndNoParenthesis()
{
var nolock = new NoLockAlt();
Assert.AreEqual("SELECT * FROM HumanResources.Department",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department NOLOCK"));
}
/// <summary>
/// IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsAtTheEndWithParenthesis
/// </summary>
[Test]
public void IfFindAndReplaceNoLockReturnsWithoutNoLockWhenNoLockIsAtTheEndWithParenthesis()
{
var nolock = new NoLockAlt();
Assert.AreEqual("SELECT * FROM HumanResources.Department",
nolock.FindAndReplaceNoLock(
"SELECT * FROM HumanResources.Department (NOLOCK)"));
}
}
}```
Hey, those look familiar.
All red.
And then the implementation.
```csharp
using System;
using System.Collections.Generic;
using System.Linq;
namespace NoLock
{
public class NoLockAlt: INoLock
{
public string FindAndReplaceNoLock(string noLock)
{
var noLockList2 = new List<Tuple<string,string>>() { new Tuple<string,string>("WITH (NOLOCK,","WITH ("), new Tuple<string,string>(",NOLOCK)",")")};
foreach (var n in noLockList2.Where(n => noLock.Contains(n.Item1)))
{
return noLock.Replace(n.Item1, n.Item2).Trim();
}
var noLockList1 = new List<String> {"WITH NOLOCK", "WITH (NOLOCK)", "(NOLOCK)", "NOLOCK"};
foreach (var n in noLockList1.Where(noLock.Contains))
{
return noLock.Replace(n, "").Trim();
}
return noLock;
}
}
}```
Yep these are the little things I do to keep myself busy.
[1]: /index.php/DataMgmt/DBAdmin/powershell-replace-with-regex
Chris is awesome.