Format Data based on Regular Expression

Question

Is there a way to 'apply' a regular expression formatting to an unformatted value of data?

So for example, if my current data contains a field for Social Security Number like this "123456789", could I somehow feed in the Regular Expression requirement like "[\d]{3}-[\d]{2}-[\d]{4}" to reshape all SSN values to "123-45-6789"?

Thank you!

binay2448 · Answer

Here is my solution..

Format Data based on Regular Expression.yxmd

atcodedog05 · Answer

Hi @scollier1993

Edit: same as the above solution by @MarqueeCrew. And he has a good point string function are faster than regex 🙂. Just make sure data type of SSN is string before using the above functions.

You can use regex_replace like below

REGEX_Replace(ToString([SSN]), "(\d{3})(\d{2})(\d{4})", "$1-$2-$3")

Workflow:

Hope this helps : )

SSN.yxmd

MarqueeCrew · Answer

@scollier1993 ,

Yes, but NO!!!

Regex_Replace([SSN], "(\d3)(\d2)(\d4)", '$1-$2-$3')

Left([SSN],3) + "-" + Substring([SSN],3,2) + "-" + Right([SSN],4)

The String functions (2nd) expression is much more efficient.

Cheers,

Mark