Search for Records Containing a Specified String Group in Multi Fields — From SQL to SPL #40

Problem Description & Analysis:

A table in an Oracle database has multiple string fields.

Task: 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:

Solution Highlights:

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.

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.

🌟 Try esProc SPL for Free — esProc SPL FREE Download.