Choose Language Hide Translation Bar
Highlighted
AlexS
Level III

REGEX HELP with White Spaces

Hello

I can do what i want for regex in an editor but I'm having trouble switching it over to use for JMP.

The following does what i need it do to in the editor

([E]|[e])[2]|(231)

 

i can't seem to get to work exactly like i need it to in JMP i got as far as the below

Regex( :TEXT, "(E2|e2|231\d?)", "\1" )

 

it will pick up E2 or e2 no problem but for 231 i need it to only extract when there is whitespace either side of it.

I have been looking through the community but can't find exactly what I need.

In an ideal world it will display all results in the column not just one?

 

I'm sure someone out there can tell me seconds how to do it

 

Alex is the name, Power BI/ SQL /JMP is my game
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
gzmorgan0
Super User

Re: REGEX HELP with White Spaces

@AlexS ,

 

Mark, @markbailey , provided you with the pattern to find any number in the first pattern  "space + any number + space"

 

Maybe I misread your response but it seemed  to me you were looking for the specific sequence  space + 231 + space.

 

If yes, an alternative solution is to use the Contains() function. The code is a little more readable. Note REGEX returns a string, so if you want the number you need the Num() function. A table with both functions is attached to this post.

If( Contains( :Text, " 231 " ),
	231
)

 

image.png

View solution in original post

Highlighted
txnelson
Super User

Re: REGEX HELP with White Spaces

Here is a formula that should work to get what you want.  The column you apply this to, needs to be a Character Column.

If( Row() == 1,
	getValues = Function( {},
		returnVal = "";
		If( Contains( :Text, " 231 " ),
			returnVal = "321"
		);
		If( Contains( :Text, " e2 " ),
			If( returnVal != "",
				returnVal = returnVal || ", "
			);
			returnVal = returnVal || "e2";
		);
		If( Contains( :Text, " E2 " ),
			If( returnVal != "",
				returnVal = returnVal || ", "
			);
			returnVal = returnVal || "E2";
		);
		returnVal;
	)
);
theValue = getValues();
 
Jim

View solution in original post

7 REPLIES 7
Highlighted
gzmorgan0
Super User

Re: REGEX HELP with White Spaces

@AlexS ,

 

\s is usually the symbol for white space or \sb. However, it will be easier to give your a response if you provide an example of one row of column :text and what you are trying to extract, Make something up if it is proprietary.

Highlighted
AlexS
Level III

Re: REGEX HELP with White Spaces

@gzmorgan()

So example text would be

Jsjsjjs 231 jdjdjdj 2377474742311

I don’t want it from the second numeric sequence just when there is white space either side at the moment it would pick up 2311 when all I want is 231 if there is one

Thank you
Alex is the name, Power BI/ SQL /JMP is my game
Highlighted

Re: REGEX HELP with White Spaces

This example shows the expression to get the second part of your target based on the example that you provided.

Regex(
	"Jsjsjjs 231 jdjdjdj 2377474742311",
	"\s(\d+)\s",
	"\1"
);
Learn it once, use it forever!
Highlighted
gzmorgan0
Super User

Re: REGEX HELP with White Spaces

@AlexS ,

 

Mark, @markbailey , provided you with the pattern to find any number in the first pattern  "space + any number + space"

 

Maybe I misread your response but it seemed  to me you were looking for the specific sequence  space + 231 + space.

 

If yes, an alternative solution is to use the Contains() function. The code is a little more readable. Note REGEX returns a string, so if you want the number you need the Num() function. A table with both functions is attached to this post.

If( Contains( :Text, " 231 " ),
	231
)

 

image.png

View solution in original post

Highlighted
AlexS
Level III

Re: REGEX HELP with White Spaces

@gzmorgan0,
,

Both will be required in some form but specifically to this request I need it for ‘231’ so thanks to both of you.

How would I get it to search for all 3 and then put it all in one column if it’s there? Is it just a case of creating multiple Regex along with concatenation of columns?

So for example I am looking for E2 or e2 or 231 but it will return one of those only. What I’d like is for it to return everything it finds?

Thank you for your direction and help
Alex is the name, Power BI/ SQL /JMP is my game
Highlighted
txnelson
Super User

Re: REGEX HELP with White Spaces

Here is a formula that should work to get what you want.  The column you apply this to, needs to be a Character Column.

If( Row() == 1,
	getValues = Function( {},
		returnVal = "";
		If( Contains( :Text, " 231 " ),
			returnVal = "321"
		);
		If( Contains( :Text, " e2 " ),
			If( returnVal != "",
				returnVal = returnVal || ", "
			);
			returnVal = returnVal || "e2";
		);
		If( Contains( :Text, " E2 " ),
			If( returnVal != "",
				returnVal = returnVal || ", "
			);
			returnVal = returnVal || "E2";
		);
		returnVal;
	)
);
theValue = getValues();
 
Jim

View solution in original post

Highlighted
AlexS
Level III

Re: REGEX HELP with White Spaces

Excellent thank you for your time.

Alex is the name, Power BI/ SQL /JMP is my game