How to filter contain function with unordered string ?

0
Hi expert, I have a string field in my database and the value is like this (example 3 rows data): 1. bedroomone with wall 2. bedroomonetwo without wall 3. bathroom without X I need to filter the data list with this input string -> 'wall bedroom', when I use contains($fieldString,'wall bedroom') the return is empty, I expect the output like this (2 rows data) :   1. bedroomone with wall 2. bedroomonetwo without wall   if I use contains($fieldString,'bedroom'), I can get the result I want, but I have to use unordered string to get the result I want.   is there a way how to get the result if my input string is 'Wall bedroom'?  
asked
4 answers
5

How about first splitting the input by space, using CommunityCommons’ funtion “StringSplit” and then:

contains($fieldString,'wall') and contains($fieldString,'bedroom')

 

answered
4

Hey Zacky,

I have written a blog on this topic and explained it thoroughly. 

https://medium.com/@rishabh.shandilya/create-your-own-way-of-filter-by-npe-non-persistant-entity-and-microflow-4ffd11e52fb0

 I am also trying to get it published on mendix community also, so that it can help other people also.

 

Hope it helps!

answered
0

Hi Zacky,

 

If you want to search with the database field containing all keywords:

  1. Split the string on spaces to individual keywords.
  2. foreach item: run a query on the database: contains($fieldString,'keyword')
  3. merge the resulting lists using list operation intersect

If you want to search with the database field containing one of the keywords: Use a list operation union at Step 3

Hope this helps!

answered
0

Hi there,

It seems like you want to filter your database based on a specific input string, 'Wall bedroom,' and you're encountering issues when using the 'contains' function. If I understand correctly, you'd like to retrieve rows that contain both 'wall' and 'bathroom,' even if they are in a different order in the input string.

To achieve this, you can use a combination of functions. Here's a possible solution:
SELECT *
FROM your_table
WHERE (LOWER($fieldString) LIKE '%wall%' AND LOWER($fieldString) LIKE '%bedroom%');
This query will return rows where the 'fieldString' contains both 'wall' and 'bedroom,' regardless of their order in the input string. We're using the 'LIKE' operator with '%' wildcards to search for these terms in a case-insensitive manner (using 'LOWER' to ensure case insensitivity).

answered