Find rows that contains specific text

[#1]

All,

I got stuck on a seemingly easy step.

My table is compiled from multiple CSV files. Where those files were previously concatenated, there is one service row that is empty except first three columns where it says something like "### Switching Logging: datetime" in the first column and then from which file in second coumn and to what file in third column. So, a trash row that I want to delete. The only common thing about those rows is word "Switching" in the first column. Plus the name of the first column has semicolon, something like index:date and I need to keep it that way. How do I find and delete those rows without creating new columns?

I tried something like this:

db_test << Get Rows Where (Contains("Switch", Column(1)));

But it doesn't work, apparently Contains() only takes columns that are refered to as :columnname. But since I have ":" in the name of my column so I cante refer to it as ":index:date". Even if I rename the column, it apparently doesn't work.

How to do this in one line?

Thanks! 

[#2]

Ok, it works this way:

db_test << Get Rows Where (Contains(:"DataIndex:date", "Switch"));

However, if I put Column(1) instead of  :"DataIndex:date", it errors out. Any advice on how to refer to a column using its position only?

[#3 · Accepted solution]

I think this may be the solution you are looking for:

Names Default To Here( 1 );
db_test  = Open( "$SAMPLE_DATA/big class.jmp" );


db_test << get rows where(contains(as column(3),"F"))

[#4]

Jim's suggestion is the best solution.  As an FYI, attached is legacy syntax that was commonly used prior to As Column(). You can use a column reference in a formula, however, you need empty brackets to represent the rows, the values of the column. 

Names Default To Here( 1 );
db_test  = Open( "$SAMPLE_DATA/big class.jmp" );


mycol =  Column("age");
xf = db_test << get rows where(contains(column(3)[],"F"));
xm = db_test << get rows where(contains(column("sex")[],"M"));
xa = db_test << get rows where( mycol[]== 13);
show (xf, xm, xa);

[#5]

Thanks! I remember there was some funky business with referring to columns, but I thought it was fixed. Apparently, it was fixed as "Column" --> "As Column"? :)

[#6]

Ok, all it took was changing Column to As Column. Awesome, thanks a lot!

[#7]

I think that this syntax might work, too:

db_test << Get Rows Where (Contains(Column(1)[ ], "Switch"));

That is, use an empty subscript (square brackets) after the Column(1) function call.