BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Highlighted
twillkickers
Community Trekker

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!

0 Kudos
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+" ) ) )
);
Learn it once, use it forever!
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
);
0 Kudos