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

Nigel Light

ApertureTip.JPG

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)

Clinton Jones

a nice catch @Nigel Light

Tanj Jagpal

Great post @Nigel Light, hopefully helps others too!

Ian Buckle

@Nigel Light great post, I'm wondering if you were aware of the AFTER function which will give you the remaining string after the detected string. There is also a Before function as well which will do the same but the other way around.

Nigel Light

Thanks - I wasn't aware of this one. Would it still work if there were 2 occurrences of the string at the start?

Clinton Jones

@Nigel Light unfortunately it doesn't do a dedupe. We can look at that as perhaps an enhancement

Clinton Jones

Looking at another function, using Strip Substring will remove all instances of a given string