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

[jt_edin]

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

[LordNeilLord · Accepted solution]

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!

[jt_edin]

Is anyone else able to clarify how to implement the solution kindly offered by @LordNeilLord ? Thanks

[LordNeilLord]

Hey @jt_edin 

I used a filter tool to do this, regex_match would be almost exactly the same, something like Regex_match(Field1, "[^\w-]") 

In Alteryx, if there is no space in the column header you do not always need to use the [] around the name. 

Neil

[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