KDB parsing through kdb strings to identify dates

Question

I am trying to parse through a column that has user queries logged in string format. For example

"select from table where date=2023.01.01"
"select from table2 where date = 2024.01.01"
"select from table3 where date >= 2022.01.01"

My goal is to extract date and save it in a different column in date format. I am doing it the following way which works for one kind

update queryDate:first each "," vs/: last each "date=" vs/: colName from tableName;
update "D"$queryDate from tableName;

This works well if the query string has "date=" but not if "date =" or "date >=" or "date>=" and definitely not if query has "date within"

Is there a good way to include all the above possibilities in the condition above?

Thanks!

Answer 1

Another approach is to do a wildcard search using ss and index in, e.g

q)show t:([] qry:("select from table where date=2023.01.01";"select from table2 where date = 2024.01.01";"select from table3 where date >= 2022.01.01";"select from table4 where date within 1993.01.01 2000.01.01"))
qry
------------------------------------------------------------
"select from table where date=2023.01.01"
"select from table2 where date = 2024.01.01"
"select from table3 where date >= 2022.01.01"
"select from table4 where date within 1993.01.01 2000.01.01"

q)wc:"[0-9][0-9][0-9][0-9].[0-9][0-9].[0-9][0-9]";

q)update dt:"D"$qry@'idx+\:\:til 10 from update idx:ss[;wc]each qry from t
qry                                                          idx   dt
----------------------------------------------------------------------------------------
"select from table where date=2023.01.01"                    ,29   ,2023.01.01
"select from table2 where date = 2024.01.01"                 ,32   ,2024.01.01
"select from table3 where date >= 2022.01.01"                ,33   ,2022.01.01
"select from table4 where date within 1993.01.01 2000.01.01" 37 48 1993.01.01 2000.01.01

Answer 2

I think a better approach would be to create parse trees from your string queries, and then extract the date constraint from the where clauses. See https://code.kx.com/q/wp/parse-trees/ for more info.

q)f:{wc:(parse x)[2;0]; wc[;2] where `date=wc[;1]}
q)raze f each ("select from table where date=2023.01.01"; "select from table2 where date = 2024.01.01"; "select from table3 where date >= 2022.01.01")
q)2023.01.01 2024.01.01 2022.01.01