topic How to write multiple 'Contains' in SQL query in Discussions

How to write multiple 'Contains' in SQL query

miguello — Thu, 06 Jun 2019 21:31:41 GMT

Hello everybody.

Let's say I have an array with strings that are values of rows that I need to pull from DB. Let's say the array is:

searchStringArray = {'qwe', 'rty', 'asd',' fgh', 'zxc'}

I can construct my WHERE statement in my query in a pretty simple way:

"WHERE 
t1.column1 IN (' " || Concat Items(searchStringsArray, " ', ' ")|| " ' )"

which when parsed would give me the following string:

"WHERE
t1.column1 IN('qwe', 'rty', 'asd', 'fgh', 'zxc')"

But what if my searchStringArray has not full strings but partial strings?

I need to simplify my script. Currently I have a query that has multiple Contains statements, which in SQL looks like this:

"WHERE
(t1.column1 LIKE '%qwe%') OR
(t1.column1 LIKE '%rty%') OR
(t1.column1 LIKE '%asd%') OR
(t1.column1 LIKE '%fgh%') OR
(t1.column1 LIKE '%zxc%')"

This is pretty cumbersome to construct from a string array. I was wondering if I can write an equivalent WHERE statement that looks something like this:

"WHERE
t1.column1 IN ('%qwe%', '%rty%', '%asd%', '%fgh%', '%zxc%')"

or something like this:

"WHERE
t1.column1 MATCH('%qwe%' OR '%rty%' OR '%asd%' OR '%fgh%' OR '%zxc%')"

or something similar that would be easier to construct using Concat Items(). I tried two examples above, although they seem to be valid SQLite syntax, they do not work in JSL. Any suggestions?

Thanks,

Re: How to write multiple 'Contains' in SQL query

vince_faller — Thu, 06 Jun 2019 23:47:22 GMT

What are you actually trying to do? Select some rows in a JMP data table?

Re: How to write multiple 'Contains' in SQL query

WhiteMenCanJMP — Fri, 07 Jun 2019 00:02:37 GMT

Use SIMILAR TO. For syntax, the below statements do the same thing. Of course, you'll have to type the "SIMILAR TO" statement in as a "custom expression" in the JMP query builder filters section. I don't think you can generate it by clicking items in the red triangle menu.

WHERE ( ("t1".column1 LIKE '%AAA%' ) OR ( "t1".column1 LIKE '%BBB%' ) )

WHERE ( ("t1".column1 SIMILAR TO '%(AAA|BBB)%' ) )

Re: How to write multiple 'Contains' in SQL query

miguello — Fri, 07 Jun 2019 16:41:46 GMT

It's pretty much stated in the first sentence of the question.
I need to dynamically construct a query string to pull data from DB. List of search terms and column name are dynamic. For one of the column names the match is not exact, so I can't use WHERE t1.column1 IN ('qwe', 'rty'...) and so on. Instead, I had to use LIKE keyword and very bulky construct. I was looking for a construct that is similar to (pun intended) IN in syntax. This keyword is 'SIMILAR TO'.

Re: How to write multiple 'Contains' in SQL query

miguello — Fri, 07 Jun 2019 17:29:38 GMT

Looks like it's working as promised. It produces identical to my current solution results.

I use it in JSL, where I assemble the query string out of some text and variable. For instance, t1.column1 is also a variable, and I have partial matches for only one of those variables for t1.column1, so I had to use something like this for one variable:

"WHERE(t1.column1 LIKE '%qwe%') OR
(t1.column1 LIKE '%rty%') OR
(t1.column1 LIKE '%asd%') OR
(t1.column1 LIKE '%fgh%') OR
(t1.column1 LIKE '%zxc%')"

making it a pretty complex thing to assemble something like that from stringColumnName = "column1" and searchStringArray = {'qwe', 'rty', 'asd',' fgh', 'zxc'}

While for other column variables it comes to much simpler form of:

"WHERE 
t1."||stringColumnName||" IN (' "||Concat Items(searchStringsArray," ', ' ")||" ' )"

Now, thanks to @WhiteMenCanJMP and his SIMILAR TO this task of assembling dynamic query string is going to be much simpler.

And no worries about custom window - I script that in JSL anyways. Although Custom filter in Query Builder is the fastest way to check syntax anyways.