Power Automate: Removing Alpha Characters From a Text String

I integrated two third party vendor systems using Power Automate. A flow GETs data from one API and inserts these as new employee details via another API. These new employees' first act upon securing their position is usually to try to sabotage my integration by inserting strange characters into phone numbers, National Insurance numbers and all manner of other places that cause my POST action to be rejected. Sometimes I can't even find these weird glyphs on my keyboard and have to ctrl-c and ctrl-v them into my removal list.

If you've had the same problem, you'll know there isn't a function for bulk cleansing or replacing of characters in Power Automate. This is how I did it.

I wanted to stay away from a flow relying on SQL tables and views while transferring data to a person's new employee record. This was a problem because the POST action would fail if the Social Security number was not in exactly the right format or if a phone number had anything other than numeric characters in it.

I sat one night Googling it but didn't like any of the solutions that came up. They were all full of loops, nested 'IF' or 'REPLACE' statements, array variables and Compose actions and nothing looked very elegant. I ploughed on regardless with my Flow, thinking I'd cross that bridge when I came to it.

This afternoon I stood at the foot of that bridge wondering if I could get away with just writing the aforementioned numbers to SQL, cleansing them and then quickly deleting them afterwards. I was thinking "if I could just do this as easily as I clean up numbers in SQL" and that's when it hit me that I could.

The SQL function TRANSLATE takes three arguments. The returned result is what argument 1 looks like if all the characters in it from argument 2 are replaced by the characters from argument 3. Here's an example:

If we know in advance that a telephone number has been written as '(01234) 567890' then we know that we need to replace characters '(', ')' and [SPACE]. We could therefore write TRANSLATE( '(01234) 567890', '() ', '@@@' ).

In other words:

take '(01234) 567890' and convert '(' to '@'

then convert ')' to '@'

then convert ' ' to '@'

Note that argument 2 and argument 3 are the same length (3 characters long).

The resulting output is '@01234@@567890' and this suddenly becomes much easier to put in a REPLACE function, i.e.

REPLACE( TRANSLATE( '(01234) 567890', '() ', '@@@' ), '@', '')

which produces '01234567890'.

I added a quick variable to test my plan and populated it with a phone number from the API call.

Then I added an Execute SQL Query step with my usual connection. It really doesn't matter what connection is used here as we don't use or even mention a table.

The query has been enhanced to cover a whole spectrum of alpha characters and it starts out hardcoded (i.e. without the dynamic "mobile" part) to make the step after it easier to add.

Step 3 is me reusing the same variable again to catch the output from Step 2. It encased itself in an Apply To Each loop, but that doesn't matter as the query can only return one result.

Lastly, go back to the SQL step and insert the Dynamic part.

The following picture is the result after a successful run.

Clearly it worked and the phone number has been cleansed of plus signs and spaces. Next step was to apply this to all the elements that need formatted and tidy everything up.

So there you go. I hope that helps someone. If so, please consider treating me to a wee something.