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