Is there a way to use a regex for defining all the columns in a select statement. something like
select myColumnPrefix* from myTable
that would show all columns that start with myColumnPrefix?
Add a comment
|
2 Answers
Not using qSQL, however you can use a regex to get the column names and then use a functional select. For example,
c: cols[myTable] where cols[myTable] like "myColumnPrefix*";
?[myTable;();0b;c!c]
Or as a one-liner,
?[myTable;();0b;{x!x@:where x like "myColumnPrefix*"} cols myTable]
Comments
If you really want to use qSQL (not advised - difficult to read/maintain) then you could expand on what @ostewart suggested:
Define table:
q)t:([]foo1:1 2;foo2:3 4;foo3:5 6;bar1:1 2;bar2:3 4;bar3:5 6)
q)t
foo1 foo2 foo3 bar1 bar2 bar3
-----------------------------
1 3 5 1 3 5
2 4 6 2 4 6
Extract columns of interest and prepare as string:
q)c:", " sv string cols[t] where cols[t] like "foo*";
q)c
"foo1, foo2, foo3"
Join columns to select query and value expression:
q)value "select ",c," from t"
foo1 foo2 foo3
--------------
1 3 5
2 4 6
Comments
