community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE
Announcement | We'll be doing maintenance between pm 4- 6pm MT, which may impact your experience. Thanks for your patience as we work on improving the community!

Function similar to SQLs Patindex

Highlighted
Meteor

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

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:

 

([\^\-\.\d]*)

 

20181207-RegEx.png

 

 

 

 

 

 

 

 

 

 

 

Meteor

awesome thank you very much

Meteor

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

([\^\-\.\d]*)

 

(        ) = 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

Meteor

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

Labels