Removing an unwanted intermittent string from the start of an alphanumeric field

At St. James’s Place we needed to remove a string, that occurred intermittently, from the start of an alphanumeric dataset. The “Trim” function didn’t quite meet our needs so we managed to achieve this by creating a new column in a “Transform” step as follows :

1. “Replace” function, replacing the string that you are searching for with nulls
2. Feed this into a “First Non Null” function
3. (this is either 1 if the string hasn’t been found or the position of the first character after the matched string if it existed)
4. Feed this into a “Substring” function, with the start and length equal to 1
5. (this will give you the first required character – either the first character or the first character after the string if present)
6. Feed this into a “Position” function, searching the whole string and looking for the results from iii)

Finish by feeding this into a final “Substring”, searching the whole string, starting from the number returned from iv) for length 0 (which takes you to the end of the string)