RaqForum 41 No.
9 View  • 
     ...

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 spaces. 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

SPL code:

　A

1

=orcl.query("select * from tb")

2

=argA.split(" ")

3

=A1.select(A2\~.array()==[])

A1: Query the database through JDBC.

A2: Split the parameter into a string set by spaces.

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.

Question source：https://stackoverflow.com/questions/78371969/search-for-multiple-strings-in-multiple-columns-in-oracle-sql


 
 •  9 View  •