212233_en-US

liliconcom

How to write multiple 'Contains' in SQL query

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<BR />(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, 

M 

 

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

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.

Re: How to write multiple 'Contains' in SQL queryIt'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

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

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