The Tableau CONTAINS function is one of the string functions that helps to perform search operations. It will search each row of a given column for the given Substring. If the specified Substring is present in the original, it will return boolean True. Otherwise, it returns False.
This article uses an example to show how to perform the string search using the Tableau CONTAINS function. The syntax of the string CONTAINS function is as shown below.
CONTAINS(Original_String_Column, Substring)
From the above syntax, the Tableau CONTAINS function searches the Original String Column for the Substring and returns True if it finds else false.
To demonstrate this Tableau CONTAINS function to search string or Substring, we used the below report using the AdventureWorksDW SQL database. To create the below report, Drag and drop the SalesAmount to the Columns shelf and the English Sub category Name into the Rows shelf. By default, it displays a Bar chart; Otherwise, use the Show Me window or change the Mark type to Bar. For the remaining charts in Tableau, please click here.
Next, add the English Product Name to the Color shelf to add the colors to the Bars and click the T button to add the Data labels. As there are few NULL values, we filtered those NULLs by adding the Sales Amount measure to Filters. Please visit the SQL Source and SQL Tutorial article to understand the Data Source.
Tableau CONTAINS function to search string
Create a calculated field to write the built-in Tableau CONTAINS function. Go to the Analysis Tab and choose the Create a Calculated Field option, or right-click the empty space below the MeasureValue and write the following expression.
CONTAINS([English Product Subcategory Name], "Bike")
The above string function expression will check or search each English Product Subcategory Name for the substring Bike. If it is present, it returns boolean True; Otherwise, False. For all the built-in functions >> Click Here!
Please add the Check calculated field to the table to see the output. If you observe, the Tableau CONTAINS function returns True for Road Bikes, Mountain Bikes, Touring Bikes, Bike Stands, and Bike Racks. For all remaining rows, it returns False.
The above approach will search for one particular Substring inside the original string. However, you can use the CONTAINS function to search strings using parameters dynamically.
To allow the users to choose the search term for the Tableau CONTAINS function, create a parameter to enter the string words. To do so, right-click under the Measure Values empty space and select the Create Parameter option.
It will open the following window. Let me use the Choose Item as the Parameter Name, select String as the Data type, and add a list of parameter values, i.e., Mountain, Gloves, Bike, Cap, Jersey, Tire, and Bottle.
We need a calculated field to utilize the parameter within the report. So, create one more calculated field and write the below parameter expression. Here, the Tableau CONTAINS function accepts a parameter as the second argument. So, based on the parameter value, the string search will happen.
CONTAINS([English Product Name], [Choose Item])
The next step is to add these fields to the report. Right-click on the Choose Item parameter and select the Show Parameter option. Next, drag and drop the String Search Calculated Field onto the Color shelf. The screenshot below shows that all the product names containing the Mountain are in orange and remain in blue.
Let me change the string parameter value to Tire to see the change in the Tableau CONTAINS function report.
Instead of manually entering all the values for the parameter, we can use the column as input. To do so, click the down arrow beside the Choose Item and click the edit parameter option. Next, choose the correct dataset and the column field to fill the list of items. We use the color field present in the DimProduct table for demonstration purposes.
Let me select a Black color to highlight the product names containing black color.
Instead of assigning an orange color for the chosen value and blue for the remaining, we can remove the non-matching items with Filter actions. Add the Product Subcategory to the Color shelf and String Search to the Filter Shelf. A new pop-up window will open once you place the String Search Calculated Field on the Filters shelf. Choose the True option.
The below Tableau CONTAINS function image search for the Balck string shows only product names containing the black color. Please refer to the Filters article.
