topic Re: Select Where(Contains()) Usage in Discussions

Select Where(Contains()) Usage

twillkickers — Thu, 17 Jan 2019 21:11:58 GMT

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!

Re: Select Where(Contains()) Usage

txnelson — Thu, 17 Jan 2019 22:53:31 GMT

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);

Re: Select Where(Contains()) Usage

Mark_Bailey — Fri, 18 Jan 2019 13:37:39 GMT

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+" ) ) )
);

Re: Select Where(Contains()) Usage

pmroz — Fri, 18 Jan 2019 18:33:05 GMT

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
);