How to select Rows on a column with a RegEx

[#1]

Hi, I want to select all the rows that start with the word "Insert" on a column that has a very complicated name: "Aligned_bin_on_ref(ref[+/-]strand:start-end".  I can select any rows using some of the other columns but the name of this one is somehow being interpreted as "code". 

I am also trying to use a Regex because the "values" on the column may contain more than one instance of the word "Insert" and I want to select only those that start with "Insert" 

This is the basic code that I have with some other code that I have tried commented out. Attached is a sample of the table.

I will appreciate any pointers, 

Thanks!

Alfredo

//----------------- INITIALIZE ---------------
Names Default To Here( 1 );

dt = Current Data Table ();
dt << Clear Select;


//RefCol= "Aligned_bin_on_ref(ref[+/-]strand:start-end";

// dt << Select Where (:Name("Aligned_bin_on_ref(ref[+/-]strand:start-end"), "Insert" );

SelRows = dt << Get Rows Where (RegEx(As Column("Aligned_bin_on_ref(ref[+/-]strand:start-end") , "^Insert") == "Insert");

//InsertDepth = dt << Subset( (SelRows) );

[#2]

dt << select where(
	Contains( :"Aligned_bin_on_ref(ref[+/-]strand:start-end"n, "Insert" ) == 1
)

[#3]

 Hi Jim,

I am still getting a "Unresolved Column at row 1 in access or evaluation of 'Aligned_bin_on_ref(ref[+/-]strand:start-end' , As Column/*###*/()" 

I tried your solution with and without the "n" after the column name, just in case it was not a typo.

BTW I am using JMP 16.0.0

dt << select where(
	Contains( :"Aligned_bin_on_ref(ref[+/-]strand:start-end", ,"Insert" ) == 1 );

[#4 · Accepted solution]

You did not copy the JSL correctly.  You have 2 commas after the column name, and no trailing "n"

dt << select where(
	Contains( :"Aligned_bin_on_ref(ref[+/-]strand:start-end"n , "Insert" ) == 1
)

I just realized my code had 2 commas, which was an mistake....I have corrected my entry

[#5]

Thank you very much Jim.  After I added the missing ")" parenthesis is working like a charm.  This is going to save me quite a few hours. Cheers!

[#6]

Contains(...) is a really interesting function. It seems like it might return just true or false, true if the test string is found anywhere in the source string, and false if not.

It actually returns the position (of the first occurrence) of the test string in the source, and 0 if not found. Since 0 means false and non-zero means true, Contains(...) will work just fine if you expect the true/false result.

But, as Jim's example shows, you can use the position of the first occurrence to determine if it is at the beginning. This will be more efficient than regex(...) which will spin up more machinery to do this easy test.

Contains is easier to read: your original regex test might look like this:

dt << select where( !Is Missing( Regex( dt:"Aligned_bin_on_ref(ref[+/-]strand:start-end)"n, "^Insert" ) ) );

because regex(...) returns a missing value when it fails to match.

In either case, a comment will help the next person that maintains the code, either /* ==1 to make sure it is at the start */ or /* regex returns missing if not found at the ^ start */

edit: regex(...)=="Insert" would also work, but the compare against a missing value doesn't return true or false so I avoid it.

[#7]

Dear Craige, thank you very much for the great JSL lesson!

Very much appreciated.

Alfredo