Data validation to check character types in string field - letters, numbers and hyphens

I need help from someone more experienced please to quickly point out the most concise or elegant way (perhaps regex?) to validate a field based on its character types. I want to validate that my field contains only letters (A to Z), digits (0 through 9), and only the character "-" (a hyphen).

If the field contains any other punctuation (eg underscore, period, comma) I want it to fail the test.

So I'm looking for a filter or regex tool to do the above, and I think someone will know better than me! Thanks

Regex

Accepted answers

LordNeilLord

Hey @jt_edin

In the formula tool you could use a custom filter with regex like this:

[Field1] = RegEx_Replace(Field1, "[^\w-]","")

The regex part [^\w-] means anything that isnt a alphanumeric character (\w) and a hypen (-)

All comments

LordNeilLord

Hey @jt_edin

In the formula tool you could use a custom filter with regex like this:

[Field1] = RegEx_Replace(Field1, "[^\w-]","")

The regex part [^\w-] means anything that isnt a alphanumeric character (\w) and a hypen (-)

jt_edin

Thanks. Is this a Filter tool or a Formula tool? How would I embed that within a Regex Match instead?

And, how did you get it to work without brackets [ ] around Field1? Would it be possible to post the workflow please? Thanks!

monsieursnow

Hello

I faced a similar problem and I found my own method: Formula Tool.

I didn't use Regex, but instead I used the STRSPN function

If STRSPN([Field1], "-ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789") = Length([Field1]) = -1

Then "Pass"

Else "Fail"

Endif

//this only tests uppercase alphabets, 0-9 and hyphen

You can read about this function here:

https://help.alteryx.com/20213/designer/conversion-functions

Not as elegant as regex, but it was easier for me to read the code

Hope it helps anyone encountering the same problem

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703