⬅ Forum: Building VoltDB Applications

Post: VoltDB SQL regular expression function

VoltDB SQL regular expression function
ml_mobile
Nov 5, 2015
Does VoltDB Has Similar Regular Expression Function Like Oracle (regexp_substr)?

If not, can we implement similar in Java that can be used by sqlcmd during query like user defined function?

Can you please share sample/example code on how to do achieve this?

Thanks.
jpiekos
Nov 5, 2015
We do not currently support a regex function. Our SQL column functions are listed here: https://docs.voltdb.com/UsingVoltDB/AppSqlFuncs.php

One workaround would be to create a stored procedure that post-processed the query's result set, applying a regex per your specific requirements. Then, from sqlcmd, you could invoke that stored procedure. Note however, that you would have to pre-define the SQL SELECT in the stored procedure, so that is not a general purpose method, but may satisfy (very) specific query needs.

Can you elaborate on the type of VoltDB application you are building?

John
ml_mobile
Nov 5, 2015
Hi John,
Thanks for replying.

Basically I am trying to make use sqlcmd to generate some report with Perl.
One of the SELECT query involving the 'dissecting' the date value from, e.g., some random filenames stored in a column.
The regex is need to return matching date value if got record for the next/subsequent SQL to gather the results from table.

You mentioned about the post-processed workaround, can you share me more details as well sample code to achieve?

Also, can I make use an example that came across in IBM DB2.
Basically am interested to use the method regexpSubstr in Regexp.java.

Can you please share your thoughts how such is feasible in VoltDB if using "load" and/or "create procedure from class"?

Thanks in advance.
jpiekos
Nov 6, 2015
Check out our voter example, in particular this stored procedure: https://github.com/VoltDB/voltdb/blob/master/examples/voter/procedures/voter/ContestantWinningStates.java
While it does not do pattern matching, it does process a result, do string comparisons (substitute your pattern matching logic) and builds a VoltTable result set that it passes back to the caller.

Yes, "create procedure from class" is the way to make this transaction (this stored procedure) callable from a program or directly/ad hoc, from sqlcmd and VoltDB Management Center.

John