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

Options
Nigel Light
Nigel Light Contributor
edited December 2023 in General

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)

Tagged:

Comments