From SQL to SPL：Search for records containing a specified string group in multi fields

A table in an Oracle database has multiple string fields.

fname	lname	street	city	return from select
John	Doe	mainstreet	New York	yes
John	Doe	street	New York	no
Doe	John	mainstreet	New York	yes
mainstreet	John	doe	New York	yes
c2	c2	c3	c4	c5

Now we need to input a parameter that contains multiple strings separated by commas. We need to find the records in the table that contain all these strings in the fields, or those records where the set of fields is a superset of the parameter. For example, when the parameter argA="street,John,Doe", the calculation result is as follows:

fname	lname	street	city	return from select
John	Doe	street	New York	no

We can convert string parameters and each record into sets respectively, and filter out those records where the difference set of the two is an empty set. But it is not easy to split string parameters into sets in SQL. Regular expressions and CONNECT BY functions are required, and the code is quite cumbersome. Some databases do not support this writing method and can only implement it using custom functions or stored procedures, which is even more troublesome. Converting a certain record into a set is also very cumbersome, usually requiring writing comparative judgments for each field, which is very tedious.

SPL provides directly usable functions that can easily convert string parameters and records into sets for intersection, union and difference operations.

	A
1	=orcl.query("select * from tb")
2	=argA.split@c()
3	=A1.select(A2~.array()==[])

A1: Query the database through JDBC.

A2: Split the parameter into a string set by commas. The split function splits a string using the specified delimiter, @c indicates that the delimiter is a comma.

A3: Filter out records where the difference between the parameter and the field set is an empty set, which is equivalent to finding records where the set of fields is a superset of the parameter.