- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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+" ) ) )
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
);