cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
twillkickers
Level III

Select Where(Contains()) Usage

I often find that the Contains() function inside the Select Where() function can be convenient. What if I would like to select all rows where a designated column contains the string "a" when it is followed by a number? For example, in a column with the values "a", "a12", and "absent", only the row with "a12" would be selected. Thanks for any tips!

3 REPLIES 3
txnelson
Super User

Re: Select Where(Contains()) Usage

This will work if the numbers are only after the first occurance of the letter "a" 

dt << select where(contains(:name,"a") & 
IsMissing(num(substr(:name,contains(:name,"a")+1,1)))==0);
Jim

Re: Select Where(Contains()) Usage

Another option:

 

Names Default to Here( 1 );

dt = New Table( "Select by Criterion",
	New Column( "Text", Character, Values( { "a", "a12", "absent" } ) )
);

dt << Select Where(
	Not( Is Missing( Regex( :Text[], "a\d+" ) ) )
);
pmroz
Super User

Re: Select Where(Contains()) Usage

Nice clean solution Mark.  Gotta love those regular expressions.  I'm curious why you have [] after :Text?  Seems to work without it.

BTW this also works:

dt << Select Where(
	Regex( :Text, "a\d+" ) == :Text
);