Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Function similar to SQLs Patindex


I have a field that has data of varying lengths.  i want to create a new field from this field that is the string up to a character which is not in a given set of characters.  This is because there are various symbols (letters, punctuation etc) that I would want to use to terminate the field.  In SQL, my code looked like

Patindex('%[^-.0-9]%' meaning i wanted to find the spot where the first character that did not match ^,-,., or [0,9].


Anything like this in alteryx? basically, i would want to use a findstring function and be able to look for a character that is not in a given set of values.  (i am looking for a general solution, but ifthere is a function specific to finding a character not in ^,-,.,[0,9] that would also be very helpful.)


thanks in advance!



Alteryx Certified Partner
Alteryx Certified Partner

RegEx or Regular Expressions are available in a few Alteryx tools and will definitely do the trick here. I've attached an example that only keeps the string as long as it is a string of only the characters you specified above using this expression:

















awesome thank you very much


would you be able to explain how you came up with that syntax? I don't quite see how you knew to use that expression.

Alteryx Certified Partner
Alteryx Certified Partner



(        ) = Contains the expression

[      ] = Any character in this set

* = One or more of the characters in that set


Now for the characters in that set:

\^ = "^" character

\- = "-" character

\. = "." character

\d = Any number(digit) character


ahh the d is really what was stumping me, but that makes perfect sense.  thank you very much!