strcount(X;Y)

Returns the number of occurrences of a given substring within a given string.

Syntax

strcount(X;Y)
bstrcount(X;Y)

Input

Argument Type Description
X text The string on which to apply the function

A scalar value or the name of a column

Y text The substring to search for in X

A scalar value or the name of a column

Return Value

Returns an integer value corresponding to the number of occurrences of the substring Y within the string X.

If X is N/A, the result is 0.

If Y is N/A, the result is the number in characters of X, which is equivalent to strlen(X).

Sample Usage

value substring strcount(value;substring)
'banana' 'an' 2
'banana' '' 6
'' 'an' 0

Example

The following example uses strcount(X;Y) to find whether a text value in a given column is contained as a substring within another text column.

<base table="pub.demo.retail.prod"/>
<willbe name="compare_text" value="'PEPSI'"/>
<willbe name="contains_text" value="strcount(itemdesc;compare_text)"/>
<colord cols="itemdesc,compare_text,contains_text"/>

In the table pub.demo.retail.prod, a new column named compare_text is created that has the text value PEPSI. The column contains_text consists of the results of strcount(itemdesc;compare_text), which is the number of times the text value in the compare_text column occurs in the text value in the itemdesc column. If the value in contains_text is greater than 0 for a particular row, the text value in compare_text is a substring of the text value in itemdesc.

The results of running this query would be:

You can see in the image above that the string PEPSI appears in two rows in the itemdesc column.

Additional Information

  • strcount is Unicode (UTF-8) compliant and will work with Unicode or plain ASCII text fields.
  • If passed a string argument that is not legal Unicode, it will by default signal an error (configurable as a user preference).
  • A corresponding function bstrcount can be used with non-Unicode strings (e.g., binary or legacy encodings).