How to verify whether a string exists in String Collection attribute or not using flexible search query of an item type?

[former_member620692 · Accepted solution]

The general FS Query for this requirement is as follows:

 SELECT INSTR(CAST({your-attribute} AS CHAR(10000) your-db-specific-character-encoding-syntax), 'your-search-string') > 0 FROM {your-itemtype}

Please note that the SQL functions differ from one database to another e.g. the following FS Query is specific to MySQL and running it with other databases may throw exceptions:

 SELECT CAST({urlPatterns} AS CHAR(10000) CHARACTER SET utf8) AS urlpatterns, INSTR(CAST({urlPatterns} AS CHAR(10000) CHARACTER SET utf8), 'electronics') > 0 AS found FROM {CMSSite}

The FS Query given above displays the following result:

With Oracle, you can use the following FS Query:

 SELECT {pk},{uid},UTL_RAW.CAST_TO_VARCHAR2({urlpatterns}) AS urlpatterns FROM {CMSSite} WHERE DBMS_LOB.INSTR({urlPatterns}, UTL_RAW.CAST_TO_RAW('electronics')) > 0

The FS Query given above displays the following result:

[former_member620692 · Accepted solution]

I have also posted an answer at https://answers.sap.com/questions/12750906/how-to-retrieve-the-data-and-see-for-blob-object-c.html?c... for a related problem.

[geffchang · Accepted solution]

Arvind's answer is a very interesting workaround. If the purpose is only for debugging or checking data quickly, it is probably fine. But, it's not a "best practice" or something that you should use "normally". The "java.util.ArrayList" text in the screenshot is proof that the result / content is not a normal string.

[former_member620692 · Accepted solution]

The question asked by is not about whether managing such a String data is better with a relation or StringColllection; his question is how to find a String in the data stored for an attribute of type, StringCollection and for this purpose, the FS Queries mentioned above will pass with 100% accuracy. Regarding your following comment,

The "java.util.ArrayList" text in the screenshot is proof that the result / content is not a normal string.

please note that Hyris stores StringCollection as a BLOB in the database.

[geffchang]

Duplicate of: https://answers.sap.com/questions/12759832/how-to-flexiblesearch-on-collection-type.html

[Former Member]

Thanks for your reply. your suggested link for the collection of models.

But our requirement is to check the string present in the collection of strings or not. Collection of strings are stored as a BLOB in Db level.

[geffchang]

In general, collections are not searchable using FlexibleSearch, regardless of type. To make it searchable, the recommendation is to use relations, instead of collections.