REGEX in Pentaho to clean a join column in my data
<p>I have been struggling with a certain column in my data where the source data is dirty and i cant find joins because of this.</p>

<p>So What I am trying to do is:</p>

<ol>
<li>Select the column [website_reference_number] among others</li>
<li>REGEX to review [website_reference_number] according to certain specs</li>
<li>Then I need to trim that data so that there are no in-consistencies left so that my joins will be clean</li>
</ol>

<p>In example</p>

<pre><code>if [website_reference_number] = "CC-DE-109"                &gt;&gt;&gt; Leave it like that

if [website_reference_number] = "CC-DE-109-Duplicate"      &gt;&gt;&gt; change to CC-DE-109

if [website_reference_number] = "CC-DE-109 Duplicate"      &gt;&gt;&gt; change to CC-DE-109

if [website_reference_number] = "CC-DE-109-Duplicate-Duplic" &gt;&gt;&gt; change to CC-DE-109
</code></pre>

<p>So the rules are in human terms <code>{Any 2 Letters}-{Any 2 Letters}-{AnyAmountOfNumbers}</code></p>