`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).